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

.net c# entity-framework entity-framework-6 sql-server


Here is a simple sample database model that hasProducts ascribed toCategories , whereCategoryId in Products the link between FK andCategories .


  • ItemId (PK), Int
  • VARCHAR is a product name (255)
  • INT and CategoryId (FK)


  • Integer CategoryId (PK)
  • name of category VARCHAR (255)

Only a de-normalized version of an object may be used for the.NET application data model.Product a class of entities that:

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

There doesn'tCategory class specified, and none is intended for this case.

In the Entity Framework, code comes firstDbContext I've set up the derived class andDbSet<Product> Products entities set

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

Then in theEntityTypeConfiguration , I'm trying to wire it up, but I just can't seem to get it to work properly:

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 am aware that I could construct a SQL View and then instruct EF to map to that view by usingToTable("App1ProductsView") , however in this case I'd prefer to steer clear of it.

There is no problem with a SQL ADO.NET ORM solution here. To complete the task, I may easily create my own SQL query.INNER JOIN Categories c ON c.CategoryId = p.CategoryId join. How can I implement the same inner join while filling the object using the EF code-first Fluent API?

I've come across a number of "object divided among numerous tables" themes in my study, but this is not one of them. Although (from a database standpoint) Categories and Products are two separate entities, the.NET code is intended to remain ignorant of this.

1st Attempt Fails:

This is ineffective and results in an odd inquiry (seen with SQL Server Profiler).

Config for fluent:

Map(m =>
    m.Property(t => t.CategoryName);

Finding SQL:

    [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]
10/29/2018 8:15:55 PM

Accepted Answer

11/3/2018 10:25:34 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow