code first join between table not appeared

Question

I am having 3 tables with a join tables between the user and the type tables.

1. I import the tables as code first from database, I have the contexts fine, DBset, DBSet, and

       modelBuilder.Entity<User>()
           .HasMany(e => e.Type)
           .WithMany(e => e.Users)
           .Map(m => m.ToTable("UserType").MapLeftKey("ut_use_id").MapRightKey("ut_ut_id"));
   

2. However, I have no idea how to retrieve with linq to sql a table with 3 columns User.name, User.firstname, and Type.type_description==1 ?

I started and tried

    using (MyContext db = new MyContext())
    {
        var query = (from u in db.Users....

But there is no table db.UserType ?

Thanks for your helps Cheers

Answer 1

This should get you your Users (assuming the navigational properties are set up correctly, and the navigation property is UserTypes):

using (MyContext db=new MyContext)
{
  var query=db.Users.Where(u=>u.UserTypes.Any(t=>t.type_description==1));
}

if you need the UserTypes, then:

using (MyContext db=new MyContext)
{
  var query=db.Users
    .Include(u=>u.UserTypes)
    .Where(u=>u.UserTypes.Any(t=>t.type_description==1));
}

EF will abstract away the "UserTypes" cross reference table because you don't actually need it. All you should care about is what Types are associated with the Users. The fact there is a cross reference table helping you should be an implementation detail of how SQL works.

The navigational property could be called anything, and has relatively little to do with the name of the cross reference table. If the cross reference table was called say UserTypesCrossReferences, the navigation property could still be called UserTypes. You just so happen to have a table by what EF would want to call the navigation property by default.