Turn SQL into Lambda/Linq

Question

I've been trying to turn a fairly basic piece of SQL code into Lamda or Linq but I'm getting nowhere. Here is the SQL query:

SELECT * FROM Form a 
INNER JOIN FormItem b ON a.FormId = b.FormId
INNER JOIN FormFee c ON a.FormId = c.FormId 
INNER JOIN FeeType d ON c.FeeTypeId = d.FeeTypeId
WHERE b.StatusId = 7

I tried this but it isn't doing what I want.

public Form GetFormWithNoTracking(int id)
{
    return ObjectSet
       .Where(x => x.FormId == id && 
                  (x.FormItem.Any(di => di.StatusId == (short)Status.Paid)))
       .AsNoTracking()
       .FirstOrDefault();
}

I'm trying to return only the rows from FormItem whose StatusId is Paid. However, the above returns all. I know that .Any() will check if there are any matches and if there are return all, so in this case my data, for this form, does have items who have a StatusId of Paid and some items whose StatusId is not paid so it brings back them all.

You are trying to perform an inner join with linq lamda, a quick search will find you the results you need: http://stackoverflow.com/questions/2767709/c-sharp-joins-where-with-linq-and-lambda http://stackoverflow.com/questions/9720225/how-to-perform-join-between-multiple-tables-in-linq-lambda — dougajmcdonald, Oct 27 '16 at 08:24
Right now this function should return the first (if any) `Form` that has a `FormItem` with `StatusId` `Paid`. Since your return type is `Form`, I'm not entirely sure what exactly you are actually trying to do. If I understand your question correctly, you only want the `FormItems` of the `Form` with `FormId` `id` where `StatusId` is `Paid`, ... right? — Manfred Radlwimmer, Oct 27 '16 at 08:29

score 1 · Answer 1 · answered Oct 27 '16 at 08:38

1

var query = (from a in ObjectSet.FormA
             join b in ObjectSet.FormB on a.field equals b.field
             where b.StatusId = 7
             select new { a, b})

You can join rest with same logic.

answered Oct 27 '16 at 08:38

Burk

2,969
1
23
24

Manfred Radlwimmer · Accepted Answer · 2016-10-27T08:41:39.183

This should be what you are asking for:

Get the Form with FormId = id
Of that form, return all FormItems that have StatusId = Paid

public IEnumerable<FormItem> GetFormWithNoTracking(int id)
{
    return ObjectSet
        .SingleOrDefault(x => x.FormId == id)
        .Select(f => f.FormItem
            .Where(di => di.StatusId == (short)Status.Paid))
        .AsNoTracking();
}

If you need the Form itself too, you might want to create a custom type (edit: see @Burk's answer) or return a Tuple<Form,IEnumerable<FormItem>>, a IEnumerable<Tuple<Form,FormItem>> or whatever suits your needs best instead.

Alternatively you could remove all non-paid items of the form.

public Form GetFormWithNoTracking(int id)
{
    var form = ObjectSet
        .SingleOrDefault(x => x.FormId == id)
        .AsNoTracking();

    var nonPaid = form.Select(f => f.FormItem
            .Where(di => di.StatusId != (short)Status.Paid)).ToList();

    foreach(FormItem item in nonPaid)
        form.FormItem.Remove(item);

    return form;
}

The alternative solution worked a treat for me. Thanks for everyone's help! — clueless83, Nov 01 '16 at 09:52

Turn SQL into Lambda/Linq

2 Answers2