De-normalizing data into a code first Entity Framework entity, without a SQL View

Question

First, here's a simple example database model, which has Products assigned to Categories, where CategoryId in Products is the FK relationship to Categories.

Products:

• ProductId (PK), INT
• ProductName VARCHAR(255)
• CategoryId (FK), INT

Categories

• CategoryId (PK), INT
• CategoryName VARCHAR(255)

For the .NET application data model, only a de-normalized representation of a Product is defined as an entity class:

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
}

There is no Category class defined, and for this example, none is planned.

In the code-first Entity Framework DbContext-derived class, I've setup the DbSet<Product> Products entity set:

    public virtual DbSet<Product> Products { get; set; }

And in the EntityTypeConfiguration, I'm attempting to wire it up, but I'm just not able to get it working right:

public class ProductConfiguration : EntityTypeConfiguration<Product>
{
    public ProductConfiguration()
    {
        HasKey(t => t.ProductId);

        // How do I instruct EF to pull just the column 'CategoryName'
        // from the FK-related Categories table?
    }
}

I realize that a SQL View could be created and then I could tell EF to map to that view using ToTable("App1ProductsView"), but in this example, I'd like to avoid doing so.

In a SQL ADO.NET ORM solution, there's no issue here. I can simply write my own SQL statement to perform the INNER JOIN Categories c ON c.CategoryId = p.CategoryId join. How can I use the EF code-first Fluent API to perform this same inner join when populating the entity?

In my research, I've seen a lot of "entity split across multiple tables" topics, but this is not that. Categories and Products are two distinct entities (from a database perspective), but the .NET code is meant to stay unaware of that.

Failed Attempt 1:

This does not work, and produces a strange query (seen with SQL Server Profiler).

Fluent config:

Map(m =>
{
    m.Property(t => t.CategoryName);
    m.ToTable("Categories");
});

Resulting SQL:

SELECT 
    [Extent1].[ProductId] AS [ProductId], 
    [Extent2].[ProductName] AS [ProductName], 
    [Extent2].[CategoryId] AS [CategoryId], 
    [Extent1].[CategoryName] AS [CategoryName], 
FROM  [dbo].[Categories] AS [Extent1]
INNER JOIN [dbo].[Product1] AS [Extent2] ON [Extent1].[ProductId] = [Extent2].[ProductId]

Answer 1

Short answer: MS EF6 is not designed for that, you can't do it easily.

Please read about the keys, relationships and how to configure one-to-many relationship.

EF expects your entity to have a key which is a part of a mapped table. You could use splitting, i.e. put some properties in Table1 and some to Table2, but only if both tables share the same primary key. That works for [1] -> [0..1] relationships only. What you have is a one-to-many.

EF way of mapping your db schema is to create two entities and access the category name as Product.Category.Name.

If you completely do not want to expose Category entity, you may use an internal class and a protected property, exposing category name as a sql-ignored property public string CategoryName => this.Category?.Name.

The other option is to use untracked SqlQuery. Then you will have to write SQL query yourself, as you did for the pure ADO.NET solution.

If you don't want to use EF change tracking, relationships and etc, consider a lighter ORM like Dapper, linq2db or BLToolkit.