Convert SQL Query to LINQ Lambda C#

Question

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.

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

Add your model to the question. Do you have navigation properties? — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Mar 1, 2021 at 12:14
@SvyatoslavDanyliv hi, I didnt get your question, but below mentioned solution did work properly. — oni3619
– oni3619, Commented Mar 1, 2021 at 13:29

Onkar Nirhali · Accepted Answer · 2021-03-01 12:25:20Z

1

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)

answered Mar 1, 2021 at 12:25

Onkar Nirhali

2363 silver badges8 bronze badges

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

Comments

persian-theme · Accepted Answer · 2021-03-01 12:28:14Z

1

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());

answered Mar 1, 2021 at 12:28

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

Collectives™ on Stack Overflow

Convert SQL Query to LINQ Lambda C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related