LINQ query with multiple conditions in WHERE clause

Question

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?

The where condition is applied to each row independently, Does the same row / record have two "Feature" column with multiple values. I think your query is not right. It is not an issue with Linq. I'm not familiar with Linq, but your query should be constructed as a nested query with "not in" clause — The King
– The King, Commented Dec 7, 2016 at 5:55

meganaut · Accepted Answer · 2016-12-07 05:59:13Z

5

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

answered Dec 7, 2016 at 5:59

meganaut

5032 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dean Friedland Over a year ago

This was a great answer and the one that I eventually went with to solve my problem. Thank you @meganaut

Vadim Ovchinnikov · Accepted Answer · 2016-12-07 06:09:41Z

0

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;

answered Dec 7, 2016 at 6:09

Vadim Ovchinnikov

14.1k7 gold badges68 silver badges95 bronze badges

Collectives™ on Stack Overflow

LINQ query with multiple conditions in WHERE clause

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related