Reputation: 156
I tried to use the suggestion provided here for using In operator in linq but, i am not able to convert my requirement into LINQ statement.
Below is the SQL query which i need to convert to Linq
select *
from navigator_user_field_property
where user_id = 'albert'
and field_id in (
select field_id
from navigator_entity_field_master
where entity_id = 1
and use_type = 0)
order by field_id
I want this to be converted to a Efficient Linq.
Most of the answers deal with the predetermined list of string array which is not working in my case.
Thanks
Upvotes: 0
Views: 497
Reputation: 172875
Here is an efficient and readable LINQ query:
var fields =
from field in db.navigator_entity_field_masters
where field.entity_id == 1 && field.user_type == 0
select field;
var properties =
from property in db.navigator_user_field_properties
where property.user_id == "albert"
where fields.Contains(property.field)
select property;
Look mama!! Without joins ;-)
Upvotes: 1
Reputation: 1504122
Looks like a join to me:
var query = from navigator in db.NavigatorUserFieldProperties
where navigator.UserId == "albert"
join field in db.NavigatorEntityFieldMasters
.Where(f => f.EntityId == 1 && f.UseType == 0)
on navigator.FieldId equals field.FieldId
select navigator;
Note that this will return the same value multiple times if there are multiple fields with the same ID - but I suspect that's not the case.
You could do a more literal translation like this:
var query = from navigator in db.NavigatorUserFieldProperties
where navigator.UserId == "albert" &&
db.NavigatorEntityFieldMasters
.Where(f => f.EntityId == 1 && f.UseType == 0)
.select(f => f.FieldId)
.Contains(navigator.FieldId)
select navigator;
... and that may end up translating to the same SQL... but I'd personally go with the join.
Upvotes: 2