SQL Server Exception

BIZ TMS Aurelius
1

Just been trying out Aurelius with MS SQL server databases.


I can connect to simple databases, but as soon as I try and connect to a database with Microsoft Identity tables I get the exception below:

exception class    : EMSSQLNativeException
exception message  : [FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '.'.

I have tried this on several databases and get the same error.

Any ideas?

Dave Craggs
2

Can you please specify with more details how to reproduce the problem?

How are you creating the Microsoft Identity tables in the database? What kind of SELECT are you performing to the database? In which situation the error happens?
Thank you.
3

It's simply connecting.


I will try to reproduce, but it will be sometime next week.
4

I generated a script to create a database with the same tables and that works. Strange.

5

I generate a script that included data and duplicated the error.


I'll send it you you via an email.

Dave Craggs
6
We've received it. For what is worth, the issue is that Aurelius is trying to delete the Foreign Keys in identity tables, and such name contains dots in it. 
The best way to fix it is that you update your entities to include the foreign key name so Aurelius knows the proper names in the database. For example:


    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [ForeignKey('FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId')]
    [JoinColumn('UserId', [TColumnProp.Required], 'Id')]
    FUserId: Proxy<TAspNetUsers>;
    function GetUserId: TAspNetUsers;
    procedure SetUserId(const Value: TAspNetUsers);

Do the same for all foreign keys in database so Aurelius will not try to update the foreign keys.

7

The problem is these items are automatically created by Microsoft.AspNet.Identity.EntityFramework.

8

I don't understand, what prevents you from adding the foreign key attributes in your model?

9

This is my model


    public class ApplicationUser : IdentityUser
    {
        public string Address { get; set; }
        public string Town { get; set; }
        public string PostCode { get; set; }
        public string Mobile { get; set; }
        [Required]
        public string Firstnames { get; set; }
        [Required]
        public string Surname { get; set; }
        public string FullName => Firstnames + " " + Surname;
        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
        {
            // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
            var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            // Add custom user claims here
            return userIdentity;
        }
    }
10

I'm talking about Delphi code. See my post above with the code sample.

11

The Delphi code was generated by TMS Data Modeler


12

In this case you can use customization script in Data Modeler to make it generate the [ForeignKey] attributes for you:


http://www.tmssoftware.biz/business/dmodeler/doc/web/adding-foreignkey-attribute.html

I'm not sure if you really need to use those classes from Aurelius? Are you going to deal with data inside such tables? If not, the best option is simply not to include those classes (uncheck them from datamodeler) so everything will behave as if those table simply don't exist.