Inner join to LINQ

Question

I am new to Linq. For the learning perspective, I am trying to convert this following sql query to linq query. However, this gives me nothing. No error message or anything. The sql query gives me the table but linq doesn't give me values. The SQL query:

SELECT        
Members.FirstName, 
Members.LastName, 
PhoneScreens.BaselineEligibility
FROM            
Members INNER JOIN PhoneScreens ON Members.Id = PhoneScreens.MemberId
WHERE PhoneScreens.BaselineEligibility = 'eligible'

And the Linq query is:

context.Members
            .Include(p => p.PhoneScreens)
            .Where(y => y.PhoneScreens.BaselineEligibility == "eligible")
            .ToListAsync();

EDIT: Here are the classes:

public class Member
{
   public string FirstName { get; set; }
   public string LastName { get; set; }

   public ICollection<PhoneScreen> PhoneScreens { get; set; }

   public Member()
   {
       PhoneScreens = new Collection<PhoneScreen>();
   }

}

public class PhoneScreen
{
    public string BaselineEligibility { get; set; }

    public Member Member { get; set; }
    public int MemberId { get; set; }
}

I would appreciate any help.

First note that you're using ToListAsync, which returns a task. And it also matters how you reference PhoneScreens inside the Members class. Check [this](https://stackoverflow.com/questions/2025712/extract-sql-query-from-linq-expressions) as well to see what is the request generated. — Andrew K, Feb 21 '18 at 16:49
How are you calling that linq? Since it returns a `Task>` you cannot be using it directly so you need to show a more concrete example — Camilo Terevinto, Feb 21 '18 at 16:51
The answer by @RobertKitching is completely wrong, not sure why you accepted that — Camilo Terevinto, Feb 21 '18 at 17:06
I realized after I tried. Do you have any suggestions for the Linq query? I am pllaning to add `Any` inside the `Where` clause. — GoGo, Feb 21 '18 at 17:12
@GoGo Change `ToListAsync()` to `ToList()`. Why would you add `Any` - your code looks fine. Random changes aren't how you program. — NetMage, Feb 21 '18 at 18:32
maybe you should also add your models, maybe your navigation properties are not set correctly. — hazimdikenli, Feb 21 '18 at 19:11
@GoGo you haven't explained what the problem is yet. What did you expect and what did you get? *Is* there any entry with the eligibility you expect? Are the key values in the tables correct? Post the info in the question itself. If your tables are empty, if there's no matching records or if there are no related records between the Members and PhoneScreens tables, you won't get anything — Panagiotis Kanavos, Feb 22 '18 at 13:11

score 0 · Accepted Answer · answered Mar 20 '18 at 16:02

0

This will do the work:

context.Members
            .Include(c => c.PhoneScreens)
            .Where(m => m.PhoneScreens.Any(i => i.BaselineEligibility == "eligible"))

answered Mar 20 '18 at 16:02

GoGo

2,727
5
22
34

Inner join to LINQ

1 Answers1