0

I have following SQL-query and I have to build it up with LINQ, but it's too complex to find an easy way. :-/

Here is the query first:

SELECT * FROM tbl_a
LEFT JOIN
(
    SELECT tbl_a.id AS id
    SUM(tbl_b.amount) AS amount
    FROM (tbl_a LEFT JOIN tbl_c ON tbl_a.id = tbl_c.from_this_id)
        LEFT JOIN tbl_b ON tbl_c.id = tbl_b.tbl_c_id
        WHERE (tbl_c.deleted = FALSE)
               AND tbl_b.deleted = FALSE
               AND tbl_b.status = 2
        GROUP BY tbl_a.id
) tbl_tmp
ON tbl_a.id = tbl_tmp.id
WHERE (tbl_tmp.amount Is NULL OR tbl_a.amount > tbl_tmp.amount)
      AND tbl_a.amount > 0
      AND tbl_a.item_ID = item.Id
      AND tbl_a.type_ID = type.Id
      AND tbl_a.deleted = FALSE

I must tell you that "item.Id" and "type.Id" are parameters of type long, so you can replace them with a number.

Can someone help me to make an alternative query with same result, so I can use it with LINQ? Or can someone convert this directly to LINQ?!

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
2-Lee
  • 343
  • 2
  • 10
  • See [this answer here](http://stackoverflow.com/a/12240849/106866) – jao Sep 02 '13 at 11:42
  • 1
    Please never just drop SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. – Gert Arnold Sep 02 '13 at 13:07
  • Ok, sorry, next time I know. Sorry, I had a lot of work to do, so I had no time to answer. I made a solution, but I found out, that the problem is, that LINQ to NHibernate (v3.3.3 GA + SP1) don't have implemented yet LEFT JOINS/LEFT OUTER JOINS through GroupJoin(), so I must use CreateSQLQuery for this complex queries. HQL is not easy for these complex queries for now. I like LINQ (with method syntax) more. ;) – 2-Lee Sep 25 '13 at 13:14

0 Answers0