Translate this Linq into SQL

Question

I have this linq code that I need to translate into identical SQL so I can query the database directly... I get stuck when it gets complicated. Can anyone help?

Linq

_db.BatchPaymentSplits
    .Where(bps => bps.YearSetupId == i.YearSetupId)
    .Where(bps => bps.CustomerIdEntered != null)
    .Where(bps => _db.BatchPayments
        .Where(bp => _db.Batches.Where(b => b.BatchTypeId.Equals("T"))
                                .Select(b => b.BatchId)
                                .Contains(bp.BatchId)
        )
        .Select(bp => bp.BatchPaymentId).Contains(bps.BatchPaymentId)
    )

SQL so far

SELECT * FROM BatchPaymentSplit
WHERE YearSetupId = 1
AND CustomerIdEntered IS NOT NULL

EF will tell you the SQL it's generating, I suggest you use that instead of asking here. — DavidG, Aug 02 '17 at 15:43
Google is your friend... https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework — DavidG, Aug 02 '17 at 15:47
If the resulting SQL makes you cry I would suggest you fix the EF query. — Matthew Whited, Aug 02 '17 at 18:17

score 1 · Answer 1 · answered Aug 02 '17 at 17:58

I can't say that I think the LINQ or the resulting SQL is the best way to express this query (should be using Join I think), but this is my literal translation:

SELECT *
FROM BatchPaymentSplits bps
WHERE bps.YearSetupId = i.YearSetupId AND
      bps.CustomerIdEntered IS NOT NULL AND
      EXISTS (SELECT * FROM BatchPayments bp
              WHERE EXISTS (SELECT * FROM Batches b
                            WHERE b.BatchTypeId = 'T' AND
                                  b.BatchId = bp.BatchId) AND
                    bp.BatchPaymentId = bps.BatchPaymentId)

You can translate Contains when applied to an IEnumerable/IQueryable as an EXISTS query with an = expression.

Translate this Linq into SQL

1 Answers1