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

Question

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)

There should be one for CmsContentData. You have to instantiate your derived context, that should contain a `DbSet CmsContentDatas;`. That is the correspondent of the table. — ZorgoZ, Jul 25 '18 at 04:53
Thanks for your answer, "CmsContentData_CmsKeywords" table has tow column in database that are CmsContentData and CmsKeyword PK , therefore i need relation between these two table, so I can't do something if i just have "CmsContentData" or "CmsKeyword". Should I change my DBcontext completely ? — Javad Hosseini, Jul 25 '18 at 05:20
EntityFramework supports navigation trough the virtual properties of the entity class. Which you can lazy load by default or include on select. This is how you access related entities. Have you used EF before? — ZorgoZ, Jul 25 '18 at 05:23
Yes, I had. But for adding navigation property we have to have a model for that navigation property we wanna add, don't we ? — Javad Hosseini, Jul 25 '18 at 05:35
Yes, but according to your code pasted you have both CmsContentData and CmsKeyword entities. I don't get what you are missing... — ZorgoZ, Jul 25 '18 at 06:57
This is an [XY problem](https://meta.stackexchange.com/q/66377/168269). You should tell what you're trying to achieve. What is it for which you think you need this hidden class? — Gert Arnold, Jul 25 '18 at 09:28

score 1 · Accepted Answer · answered Jul 25 '18 at 08:39

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
             });
    });

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

1 Answers1