CODEWITHSUNDEEP
c#sqllinq
oni3619
oni3619

Reputation: 153

Convert SQL Query to LINQ Lambda C#

I have to fix a query which was already written in the LINQ Lambda, I found the fix in a Simple SQL Query but now I have some trouble in converting it to LINQ Query,

Here is my SQL Query

select * from RequestItem_SubRequestItem x 
where x.RequestItem_key = 1 and x.SubRequestItem_key in (
    select o.SubRequestItem_key 
    from SubRequestItem_Entitlement o 
    inner join SubRequestItem sr on sr.SubRequestItem_key = o.SubRequestItem_key  
    where o.Entitlement_key = 2 and sr.Action = 'Add' )

And below is my LINQ C# code where I am trying to insert my fixes which include inner join.

z.Entitlements = ARMContext.Context.SubRequestItem_Entitlement
    .Where(o => o.Entitlement_key == z.AccessKey && !o.Role_key.HasValue && o.Entitlement.EntitlementConfiguration.UserVisible == true
            && (ARMContext.Context.RequestItem_SubRequestItem
        .Where(x => x.RequestItem_key == requestItemKey)
        .Select(y => y.SubRequestItem_key)
        .Contains(o.SubRequestItem_key)))
        .Join(ARMContext.Context.SubRequestItems, subrq => subrq.SubRequestItem_key, temp => requestItemKey, (subrq, temp) => subrq == temp)

Where as previously the C# LINQ code looked like this

z.Entitlements = ARMContext.Context.SubRequestItem_Entitlement
    .Where(o => o.Entitlement_key == z.AccessKey && !o.Role_key.HasValue && o.Entitlement.EntitlementConfiguration.UserVisible == true
            && (ARMContext.Context.RequestItem_SubRequestItem
        .Where(x => x.RequestItem_key == requestItemKey)
        .Select(y => y.SubRequestItem_key)
        .Contains(o.SubRequestItem_key)))

When I try to insert the JOIN in the LINQ as per my conditions then I get to see this error. enter image description here

What is my mistake? Can anybody tell me a correct way to do it?

Upvotes: 1

Views: 186

Answers (2)

persian-theme
persian-theme

Reputation: 6638

you can use this query. I exactly matched the SQL query

var query = ARMContext.Context.RequestItem_SubRequestItem
     .Where(a => a.RequestItem_key == 1 && a.RequestItem_key == (ARMContext.Context.SubRequestItem_Entitlement
     .Join(ARMContext.Context.SubRequestItems,
     right => right.SubRequestItem_key,
     left => left.SubRequestItem_key,
     (right, left) => new
     {
        right = right,
        left = left
     })
     .Where(x => x.right.Entitlement_key == 2 && x.left.Action == "Add" && x.right.SubRequestItem_key == a.RequestItem_key).Select(y => y.right.SubRequestItem_key)).FirstOrDefault());

Upvotes: 1

Onkar Nirhali
Onkar Nirhali

Reputation: 236

I think this should Suffice your need, although you might have to make changes to the other code which are dependent on your SubRequestItem_Entitlement table with {user, add}

please have a look at that. As I am sure you will have to make those changes.

.Join(ARMContext.Context.SubRequestItems, user => user.SubRequestItem_key, subreqItems => subreqItems.SubRequestItem_key, (user, subreqItems) => new { user, subreqItems })
.Where(Action => Action.subreqItems.Action == z.ApprovalAction)

Upvotes: 1

Related Questions