Validate that no properties on an entity type are mapped to the same column

[angelaki]

I have a table where authorizations for multiple objects of my database are stored. Its primary key is

• Module
• ObjectId
• UserId

Now I'm forcing two issues here:

First I'd like to set the key like this: p.HasMany(p => p.Authorizations).WithOne().HasPrincipalKey(p => new { Module = Module.Name, ObjectId = p.Id });. This does not work since EFCore can only use entity properties for relations. So if I remove the Module it works p.HasMany(p => p.Authorizations).WithOne().HasPrincipalKey(p => p.Id); but I need to think about filtering them for every query / include.

Furthermore it created a foreign key in the authorization tabe that I took care of by this:

//Match virtual relationproperty
modelBuilder.Entity<ObjectUserAuthorization>().Property("TaskId").IsRequired().HasDefaultValue(Guid.Empty).HasColumnName("ObjectId");
modelBuilder.Entity<ObjectUserAuthorization>().Property(p => p.ObjectId).HasColumnName("ObjectId");

I moved them both to the same column. Feels pretty hacky, but works. Do I use something here?

[ajcvickers]

@angelaki We're not really sure what you're trying to do here. Could you post the database schema and foreign key constraints that you are trying to map to?

[angelaki]

Hey @ajcvickers, if it's OK for you I'd first try just to explain it:

I have a table where global object authorizations are stored. Therefor the (compound) primary key contains:

• Module (char, the Module storing the authorization)
• ObjectId (uniqueidentifier, the object's id)
• UserId (uniqueidentifier, the users' id)

Now if any object uses these authorizations I join it via:

• A constant string (the module's name)
• The object's id
• The logged in user id

.Include(p => p.Authorizations.Where(a => a.Module == Module.Authorizations.Task.Name && a.UserId == userId!.Value))

I'd like to define this part a.Module == Module.Authorizations.Task.Name already with the relation p.HasMany(p => p.Authorizations).WithOne().HasPrincipalKey(p => p.Id) - but that's not possible since it is a constant value.

[ajcvickers]

@angelaki It would really help if you posted post the database schema and foreign key constraints that you are trying to map to.

[angelaki]

I do not use a foreign key I only join the required data. I guess that's a bit special requirenment, but sure, I'll send a (simplified) scheme:

CREATE TABLE [dbo].[Object.User](
	[Module] [nvarchar](20) NOT NULL,
	[ObjectId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL
 CONSTRAINT [PK_Object.User] PRIMARY KEY CLUSTERED 
(
	[Module] ASC,
	[ObjectId] ASC,
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Task](
	[Id] [uniqueidentifier] NOT NULL,
	[Number] [int] IDENTITY(100000,1) NOT NULL
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

[ajcvickers]

@angelaki We discussed this, but we don't think there is a way to map a relationship to these tables using EF Core, and it's not something we plan to add. I expect the Flitered Include you mention above is probably the best workaround.

[angelaki]

@ajcvickers I totally understand - it's a pretty specific use-case. But at least my approach got double checked by you, that's worth it, too 😉
Thank you anyway for taking it into consideration!

[angelaki]

With my workaround I'm now facing a new issue: when adding an entity I get a DbUpdateException because of a misformed SQL statement:

INSERT INTO [Object.User] ([Module], [ObjectId], [UserId], [ObjectId])
VALUES (@p0, @p1, @p2, @p3);

Any ideas for this? Or does my use-case force me to not add them to the object's collection but rather to the DbContext collection itself?

[AndriySvyryd]

@angelaki Try it on EF Core 6.0.0-rc2, if it still fails then open a new issue, including a minimal model.

[angelaki]

@AndriySvyryd yes, this issue still exists. I'll try to create a minimal model this week.

[angelaki]

Here is the minimal model. Did it now, since a fix the next time would be great, need it right now 😉

using Microsoft.EntityFrameworkCore;

using (var dbContext = new DbContext())
{
    var objects = dbContext.Objects.Include(o => o.Authorizations);
    var queryString = objects.ToQueryString();

    //Uncomment for Exception #1
    if (queryString.Contains("MyObjectId")) { throw new Exception($"Sql '{queryString} invalid: there is no 'MyObjectId'"); }

    dbContext.Objects.Add(new MyObject()
    {
        Id = Guid.NewGuid(),
        Name = "NewObject",
        Authorizations = new[]
        {
            new Authorization()
            {
                Auth = "read",
                Module = "Mod",
                UserId = Guid.NewGuid()
            }
        }
    });
    dbContext.SaveChanges();
}

public class DbContext : Microsoft.EntityFrameworkCore.DbContext
{
    public DbSet<MyObject> Objects { get; set; }
    public DbSet<Authorization> Authorizations { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Authorization>(p =>
        {
            p.ToTable("Authorization");
            p.HasKey(p => new
            {
                p.Module,
                p.ObjectId,
                p.UserId,
                p.Auth
            });
        });

        modelBuilder.Entity<MyObject>(p =>
        {
            p.ToTable("Object");
            p.HasMany(p => p.Authorizations).WithOne().HasPrincipalKey(p => p.Id); //I'd love to define the Module here already, but that's not possible
        });

        //Uncomment for Exception #1
        modelBuilder.Entity<Authorization>().Property("MyObjectId").IsRequired().HasDefaultValue(Guid.Empty).HasColumnName("ObjectId");
        modelBuilder.Entity<Authorization>().Property(p => p.ObjectId).HasColumnName("ObjectId");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.LogTo(Console.WriteLine);
        optionsBuilder.UseSqlServer("Server=localhost;Database=Demo");
        base.OnConfiguring(optionsBuilder);
    }
}

//Needs 'My'-Prefix because otherwise the key would magically fit. "MyObject" could be Task, Note, e.g. ...
public class MyObject
{
    public Guid Id { get; set; }
    public ICollection<Authorization> Authorizations { get; set; }
    public string Name
    {
        get; set;
    }
}

public class Authorization
{
    public string Module { get; set; }
    public Guid ObjectId { get; set; }
    public Guid UserId { get; set; }
    public string Auth { get; set; }
}

For the first exception no Db is needed. Here is the quite simple script for the Db the 2nd issue needs:

CREATE TABLE [dbo].[Authorization](
	[Module] [nvarchar](20) NOT NULL,
	[ObjectId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[Auth] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_Authorization] PRIMARY KEY CLUSTERED 
(
	[Module] ASC,
	[ObjectId] ASC,
	[UserId] ASC,
	[Auth] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Object](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nchar](10) NULL,
 CONSTRAINT [PK_Object] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Object] ADD  CONSTRAINT [DF_Object_Id]  DEFAULT (newid()) FOR [Id]
GO

I'm already excited what you'll say!

[AndriySvyryd]

This call maps another property to the same column:

modelBuilder.Entity<Authorization>().Property("MyObjectId").IsRequired().HasDefaultValue(Guid.Empty).HasColumnName("ObjectId");

Remove it and instead change the relationship to use the existing property as the foreign key:

p.HasMany(p => p.Authorizations).WithOne().HasPrincipalKey(p => p.Id).HasForeignKey(p => p.ObjectId);

[angelaki]

@AndriySvyryd thank you! I didn't know that these methods can be combined!

[angelaki]

Hey, since I'm seeing there was a bit more activity on my issue - is this going to be a feature? 🥳

Just having the same issue again right now: I have a simple Tag Table, where several Modules can store item's data they are aware of:

CREATE TABLE [dbo].[Object.Tag](
	[Module] [nvarchar](20) NOT NULL,
	[ObjectId] [uniqueidentifier] NOT NULL,
	[Tag] [varchar(50)] NOT NULL,
	[Value] [nvarchar](max) NULL,
 CONSTRAINT [PK_Object_Tag] PRIMARY KEY CLUSTERED 
(
	[Module] ASC,
	[ObjectId] ASC,
	[Tag] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Will something like p.HasMany(p => p.Tags).WithOne().HasPrincipalKey(p => new { Module = "User", ObjectId = p.Id }); become supported? And just since I'm curious: is this a good approach in your opinion? Or would you create a own ´Tag-Table´ for every object that is going to use some kind of tags ([User.Tag], [Task.Tag], [Note.Tag], [Car.Tag], ...? I like the idea of this generic approach since every of my modules can easily support tags for their objects.