LINQ To Entity - Inner Join issue

Question

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.

Svyatoslav Danyliv · Accepted Answer · 2022-01-14T09:09:58.403

2

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.

edited Jan 14 '22 at 09:09

answered Jan 14 '22 at 08:46

Svyatoslav Danyliv

21,911
3
16
32

Thanks for the answer. If i uncomment join why user.Name does not work? – SilverLight Jan 14 '22 at 08:54
1

Also instead of FirstOrDefault, you can just fill array `UserTypes = query.ToArray();` – Svyatoslav Danyliv Jan 14 '22 at 08:56
I have this error about your codes : `An expression of type 'UserTypes' is not allowed in a subsequent from clause in a query expression with source type 'DbSet'. Type inference failed in the call to 'SelectMany'.` – SilverLight Jan 14 '22 at 09:08
I have corrected answer. Missed that UserTypes is just entity. – Svyatoslav Danyliv Jan 14 '22 at 09:10

Guru Stron · Answer 2 · 2022-01-14T08:45:36.110

0

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.

edited Jan 14 '22 at 08:45

answered Jan 14 '22 at 08:39

Guru Stron

102,774
10
95
132

Thanks for the answer, If i uncomment join why user.Name does not work? – SilverLight Jan 14 '22 at 08:53
@SilverLight as I wrote in answer - because `user.UserTypes.Name` is not returned from the database, your just perform join without selecting any data from second table (i.e. something like `select t1.* from table1 t1 join table2 t2 on....`). You can do something like `select new{User, UserTypes}).FirstOrDefault().User.UserTypes.Name` and it should work. – Guru Stron Jan 14 '22 at 08:58
Thanks, I have this error about your codes : `Cannot convert lambda expression to type 'string' because it is not a delegate type` – SilverLight Jan 14 '22 at 09:04
@SilverLight what code exactly? Can you post the full snippet? – Guru Stron Jan 14 '22 at 09:09
Your codes in your answer - Not comemnts. – SilverLight Jan 14 '22 at 09:11
@SilverLight it is very strange - cause I have it perfectly compiled on my machine. – Guru Stron Jan 14 '22 at 09:15

LINQ To Entity - Inner Join issue

2 Answers2