CODEWITHSUNDEEP
linqlinq-to-sql
Albert Arul prakash
Albert Arul prakash

Reputation: 156

In Operator in Linq

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

Answers (2)

Steven
Steven

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

Jon Skeet
Jon Skeet

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

Related Questions