I have two related tables like below :

Users :

public partial class Users
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Users()
    {

    }

    public int ID { get; set; }
    public int UserType_ID { get; set; }
    public string Email { get; set; }

    public virtual UserTypes UserTypes { get; set; }
}

UserTypes :

public partial class UserTypes
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public UserTypes()
    {
        this.Users = new HashSet<Users>();
    }

    public int ID { get; set; }
    public string Name { get; set; }
    public string Title { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Users> Users { get; set; }
}

For access Name of UserType i wrote this linq to entity :

string[] UserTypes = new string[1];

using (Crypto_Entities entities = new Crypto_Entities())
{
    int User_ID_Integer = int.Parse(User_ID.Trim());

    var user = (from User in entities.Users
                //join UserType in entities.UserTypes on User.UserType_ID equals UserType.ID
                where User.ID == User_ID_Integer
                select User).FirstOrDefault();
    if (user != null)
    {
        UserTypes[0] = user.UserTypes.Name;
    }
}

My question is why user.Name does not work for my purpose and what is the benefit of join in linq to entity?
If i remove join as i did in my query i still can see Name field of UserType with user.UserTypes.Name.

CodePudding user response：

You do not need join if you have defined correctly navigation properties. And if you just need Name, do not retrieve full entity.

string[] UserTypes = new string[1];

using (Crypto_Entities entities = new Crypto_Entities())
{
    int User_ID_Integer = int.Parse(User_ID.Trim());

    var query = 
        from User in entities.Users
        where User.ID == User_ID_Integer
        select User.UserTypes.Name;

    var name = query.FirstOrDefault();
    if (name != null)
    {
        UserTypes[0] = name;
    }
}

If you use navigation property in query, EF automatically generates all needed joins. But if you just select whole entity without defining Include - EF will not load related data. It makes sense, because otherwise you may load almost whole database if there are a lot of relations.

CodePudding user response：

Since you have set up the relations in your entities you don't need to manually write join to load related data:

var user = entities.Users
   .Include(u => u.UserTypes)
   .Where(u => u.ID == User_ID_Integer)
   .FirstOrDefault();

As for your join being useless - EF Core translates the code into actual SQL (which you can check) and since you are not selecting any data from the joined table - it is as useless as it would be in SQL query where you have selected fields only from one table of join result.