Reputation: 47
I am a bit new to LINQ and to Entity Framework, so rather simple SQL queries are causing me a headache. I have a user input screen that allows them to select input values and the resulting query is dependent on which values the user entered. The resulting query groups up the results to present a list of unique customers
Simplified table design - A customer can be in many states
Customer
CustomerId
Name
JoinDate
State
StateId
CustomerId
So the user has to enter a customer name on the screen to fire off the query, but the state is an optional field, so my query needs to be a bit dynamic in the code. The base LINQ I have written is rather simple
dim cust = from c in ctx.Customer
where c.Name = InputFromScreen
Select c.CustomerId, c.Name, c.JoinDate, C.State
Now that I have a base LINQ query, I can use lambda expressions to further reduce the queryset, depending on the screen input (append ANDs to the where).
Example: If the user entered in a value for the join date field:
cust = cust.Where(Function(c) c.JoinDate = DateFromScreen)
However I cannot figure out how to add the Where/Group for the state since the State is a navigation, not a table and I only want one row per customers, even if the customer has multiple states
The SQL for this would be rather simple (and this is not optimal, but appending the ANDs for each item on the screen is the simplest way to example)
select c.customerid, c.name
from customer c
where c.name = 'input' and c.JoinDate = 'input' and
c.customerid in (select customerid from state where state = 'IN')
I am thinking this is rather simple.
Upvotes: 0
Views: 519
Reputation: 161012
The Linq to Entities equivalent to your SQL query would be:
var customers = from c in ctx.Customer
where c.States.Any(s=> s.State == "IN")
select new { c.CustomerId, c.Name };
This assumes you have a navigation property States
on your Customer
entity.
(Above is C# syntax, I'm sure VB is somewhat similar)
Upvotes: 1