CODEWITHSUNDEEP
c#mysqllinq
Dean Friedland
Dean Friedland

Reputation: 803

LINQ query with multiple conditions in WHERE clause

I am 6 months into my first development role and have started to use more LINQ in our repository layer to query our DB. I could really use some help with two queries I created.

IQueryable<long> clientsWithoutFeature = from cf in db.Features
                where cf.Feature != 9 && cf.Feature == 8
                select cf.Client;

IQueryable<long> clientsWithFeature = from cf in db.Features
                where cf.Feature == 9 && cf.Feature == 8
                select cf.Client;

Each client can have multiple Feature's, each one being a separate record/row.

The first query is supposed to return all clients that have a Feature of 8 but not a Feature of 9. However, it is returning all clients with a Feature of 8 whether or not the client also has a Feature of 9.

The second query is supposed to return all clients that have a Feature of 8 and also have a Feature of 9. However, it is not returning any clients.

Can someone please tell me what is wrong with my queries?

Upvotes: 4

Views: 18727

Answers (2)

Vadim Ovchinnikov
Vadim Ovchinnikov

Reputation: 14012

It's very simple. Features is table where individual row can't have both Feature value 8 and 9.
To get what you want you need JOIN:

IQueryable<long> clientsWithoutFeature = from cf1 in db.Features
join cf2 in db.Features on new { cf1.Client, IsTargetFeature = true }
    equals new { cf2.Client, IsTargetFeature = cf2.Feature = 8 }
where cf1.Feature != 9
select cf1.Client;

IQueryable<long> clientsWithFeature = from cf1 in db.Features
join cf2 in db.Features on new { cf1.Client, IsTargetFeature = true }
    equals new { cf2.Client, IsTargetFeature = cf2.Feature = 8 }
where cf1.Feature == 9
select cf1.Client;

Upvotes: 0

meganaut
meganaut

Reputation: 503

Your sql is performing as you have written it. You need to restructure your query a little bit to express what you actually intended.

I tend to use a subquery approach like so:

IQueryable<long> clientsWithoutFeature = from cf in db.Features
                where cf.Feature == 8 && !db.Features.Any(x => x.id == cf.id && x.Feature == 9)
                select cf.Client;

IQueryable<long> clientsWithFeature = from cf in db.Features
                where cf.Feature == 8 && db.Features.Any(x => x.id == cf.id && x.Feature == 9)
                select cf.Client;

Im not really sure what your primary key column is. so i just guessed it was idY

Upvotes: 5

Related Questions