How to make LINQ to multiple tables

Question

I have two EF entities: Client and Benefit. (In the database they are connected in intermediary table: "ClientsBenefits", if it's important), I connected them in intermediary class "ClientIndexData", as it was adviced on asp.net guides.

Could you advice me how to use LINQ to search clients without benefits?

I know, how to do this using SQL, but I really surprised at the LINQ specific syntax and I cannot do anything more complex than casual .Where (i => i.Name == null) :( I was trying smth like that:

clients.Clients = clients.Clients.Where(i => i.Benefits == null)

Where clients variable is:

var clients = new ClientIndexData();
clients.Clients = db.Clients
            .Include(i => i.Schedules.Select(c => c.FintessArea))
            .Include(i => i.Paids)
            .Include(i => i.Benefits)
            .Include(i => i.Tickets);
if (benefitId != null)
        {
            ViewBag.BenefitId = benefitId.Value;
            clients.Benefits = clients.Clients.Where(
                i => i.СlientIdentificator == id.Value).Single().Benefits;
        }
         ....

And the ClientIndexData class is:

public class ClientIndexData
{
    public IEnumerable<Client> Clients { get; set; }
    public IEnumerable<Benefit> Benefits { get; set; }
    public IEnumerable<Ticket> Tickets { get; set; }
    public IEnumerable<Paid> Paids { get; set; }
    public IEnumerable<Schedule> Schedules { get; set; }

}

I also will be really thankful to you if you advice me how to count benefits (or tickets) for each client (using groupBy and count) also using LINQ.

Answer 1

With LINQ ORMs you generally try to avoid joins and use navigation properties directly. You write it as if the objects were in memory and connected by regular object references and collections:

clients.Clients.Where(i => !i.Benefits.Any())

Or, less nicely and slower on SQL Server:

clients.Clients.Where(i => i.Benefits.Count() == 0)

Note, that LINQ to database queries look very much like normal LINQ to object queries. This is a skill that will benefit your in both case.

Answer 2

Your benefits will never be null, if a client has no benefits, benefits count will be 0 To get your count of benefits

clients.Clients.Select(i => i.Benfits.Count());

Note that you can select a complex object, for example

public class BenefitsCount
{
    public int ClientId { get; set; }
    public int BenefitsCount { get; set; }
}

clients.Clients.Select(i => new BenefitsCount {
     ClientId = i.ClientId,
     BenefitsCount = i.Benfits.Count()
});

This will return an IEnumerable