CODEWITHSUNDEEP
c#entity-framework
stepandohnal
stepandohnal

Reputation: 457

One entity multiple tables in EF code first correct table names

How can I map from multiple database tables to single entity in EF and get correct queries? I have a User entity:

public class User
    {
        public int Id { get; private set; }
        public string Name { get; private set; }
        public string Surname { get; private set; }
        public string Password { get; private set; }
        public string Username { get; private set; }
    }

Entity is mapped in context:

modelBuilder.Entity<User>().Map(map =>
            {
                map.Properties(p => new
                {
                    p.Id,
                    p.Username
                });


                map.ToTable("UserProfile");
            })
                // Map to the Users table
                .Map(map =>
                {
                    map.Properties(p => new
                    {
                        p.Name,
                        p.Surname
                    });

                    map.ToTable("Users");
                }).Ignore(user => user.Photo).HasKey(user => user.Id);

Sql that this mapping generates tries to use some wrong table names like User11 instead of just User and UserProfile:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent3].[Name] AS [Name],
    [Extent3].[Surname] AS [Surname],
    [Extent1].[Password] AS [Password],
    [Extent2].[Username] AS [Username]
    FROM   [dbo].[User11] AS [Extent1]
    INNER JOIN [dbo].[UserProfile] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
    INNER JOIN [dbo].[Users] AS [Extent3] ON [Extent1].[Id] = [Extent3].[Id]

Upvotes: 2

Views: 1364

Answers (1)

Faris Zacina
Faris Zacina

Reputation: 14274

The SQL is fine. The problem is actually in your mapping expression.

If you look at your mapping expression, you have mapped all the fields to the User and UserProfile table, except password. This will cause EF6 to create one additional table, with an auto-generated name User11 to store the non-mapped property (password).

To confirm that you can look at the generated tables in SQL Server you will notice that the User11 table has only the password attribute and an Id, and when you request all users like in your example above, EF6 is joining three tables to get the result (User11, User and UserProfile).

The solution is to change your mapping expression to include the password field in of the mappings, like e.g.: 

map.Properties(p => new
{
    p.Id,
    p.Username,
    p.Password
});

This will prevent EF6 to generate the User11 table. Alternatively you could ignore the property with Ignore(p=>p.Password) but that wouldn't make any sense in this case, since you need the password field in the DB.

Then you can exclude the password field explicitly in your queries if you don't want to pull it every time.

Upvotes: 1

Related Questions