CODEWITHSUNDEEP
asp.net-mvcentity-frameworkdbcontextef-model-builder
jeyhosseini
jeyhosseini

Reputation: 33

How to read from a table that is mapped with ModelBuilder in mvc c#

I have a Dbcontext where in its OnModelCreating() method I have something like this:

     modelBuilder.Entity<CmsContentData>().HasMany<CmsKeyword>(m => 
    m.CmsKeywords).WithMany(m => m.CmsContentDatas).Map(m =>
    {
    m.ToTable("CmsContentData_CmsKeywords");
    m.MapLeftKey("CmsContentDataID");
    m.MapRightKey("CmsKeywordID");
    });

I want to read from the CmsContentData_CmsKeywords table but I wonder how ? (there is no CmsContentData_CmsKeywords model in the project solution)

Upvotes: 0

Views: 621

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30502

Apparently you have designed a many-to-many relation: every CmsContentData has zero or more CmsKeyWords, every CmsKeyword is used by zero or more CmsContentData.

In a relational database this many-to-many relationship is implemented using a junction table. This table is the one you mentioned in your DbContext.OnModelCreating.

You are right, you won't add this junction table as a DbSet in your DbContext. Your classes will be like:

class CmsContentData
{
    public int Id {get; set;}

    // every CmsContentData has zero or more CmsKeyWords (many-to-many)
    virtual ICollection<CmsKeyWord> CmsKeyWords {get; set;}

    ... // other properties
}
class CmsKeyWord
{
    public int Id {get; set;}

    // every CmsKeyWord is used by zero or more CmsContentData (many-to-many)
    virtual ICollection<CmsContentData> CmsContentData{get; set;}

    ... // other properties
}
class MyDbContext : Dbcontext
{
    public DbSet<CmsContentData> CmsContentData {get; set;}
    public DbSet<CmsKeyWord> CmsKeyWords {get; set;}
}

This is everything Entity Framework needs to know to detect that you designed a many-to-many relationship. It will create the two tables for you and the junction table, even without your code in OnModelCreating.

Only if you are not satisfied with the default identifiers for tables and columns you need the code in your OnModelCreating.

But if I have no reference to the junction table, how can I do the joins?

Answer: Don't (group)join, use the ICollections

Example: Get some CmsContentData with all (or some of) its CmsKeyWords:

var result = dbContext.CmsContextData
    .Where(cmsContextData => ...)        // only take certain cmsContextData
    .Select(cmsContextData => new        // only select properties you plan to use:
    {
        Id = cmsContextData.Id,
        Name = cmsContextData.Name,
        ...

        Keywords = cmsContextData.CmsKeywords
            .Where(keyWord => keyWord.StartsWith(...))   // only select certain keywords
            .Select(keyWord => new                       // only select properties you plan to use
            {
                 Id = keyword.Id,
                 Text = keyWord.Text,
                 ...
            })
            .ToList(),
    });

Entity Framework is smart enough to detect that (group-)joins with your two tables and the junction table are needed.

The other way round:

Select all (or certain) CmsContentData that use some specific CmsKeyWords:

var result = dbContext.CmsKeyWords
    .Where(keyWord => keyWord.StartsWith(...)  // take only certain keywords
    .Select(keyword => new                     // select only the properties you plan to use
    {
         Text = keyWord.Text,
         ...

         CmsContextData = keyWord.CmsContextData  // I only want specific context data
             .Where(contextData => ...)           // that use this Keyword
             .select(contextData => new
             {
                 ... // ContextData properties you plan to use
             });
    });

Upvotes: 1

Related Questions