c# Lambda subqueries (linq2sql)

Question

Is it possible to write the equivalent of this TSQL statement in Lambda?

SELECT  DISTINCT
        pba1.adGroupsId
,      Amount = (SELECT pba2.value 
                  FROM   ProjectBudgetAdjustment pba2 
                  WHERE  pba1.budgetId = pba2.budgetId 
                  AND    pba1.adGroupsId = pba2.adGroupsId 
                  AND    pba2.metricId IN 
                  (
                      SELECT  id 
                      FROM    Metrics 
                      WHERE   [description] = 'Amount')
                  )
,      [Hours] = (SELECT pba2.value 
                   FROM   ProjectBudgetAdjustment pba2 
                   WHERE  pba1.budgetId = pba2.budgetId 
                   AND    pba1.adGroupsId = pba2.adGroupsId 
                   AND    pba2.metricId IN 
                  (
                      SELECT id 
                      FROM   Metrics 
                      WHERE  [description] = 'Hours')
                  )
FROM    ProjectBudgetAdjustment pba1
WHERE   pba1.budgetId IN
(
   SELECT  id
   FROM ProjectBudget
   WHERE   projectId IN
   (
      SELECT  id
      FROM    Projects
      WHERE   code = 'xxx'
   )
)

Why do you want to rewrite this query into LINQ-to-SQL/EF, don't forget the possibility to loose performance on your query. But in performance point of view you should redesign your query imho. Just rewrite the where clause into an inner join. — JSC
– JSC, Commented Feb 10, 2011 at 15:23
Lambda is returning too many rows, four disciplines each with an amount and hours, so Lambda returns 8 rows. I placed the SQL here as an illustration of how TSQL could deal with it, performance was not in mind. I quess if it is not possible I will need to revisit the db architecture. — Klaptrap
– Klaptrap, Commented Feb 10, 2011 at 15:31

sgmoore · Accepted Answer · 2011-02-10 16:52:05Z

1

I could be total wrong here, but is it something like :

ProjectBudgetAdjustment
.Where( pba => pba.ProductBudget.Products.Code == "xxx")
.Where( pba => (pba.Metrics.Description == "Hours" 
             || pba.Metrics.Description == "Amount"))
.GroupBy(   pba => pba.ProductBudget.adGroupsId )
.Select (
    r => new
    {
        adGroupsId = r.Key,
        Hours  = r.Sum(i=> (i.Metrics.Description == "Hours"  ? i.value : 0m)),
        Amount = r.Sum(i=> (i.Metrics.Description == "Amount" ? i.value : 0m))
    }
);

answered Feb 10, 2011 at 16:52

sgmoore

16.1k6 gold badges46 silver badges72 bronze badges

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

3 Comments

Klaptrap Over a year ago

The above lambda is correct except that now I have to deal with enumerating an anonymous typed object

sgmoore Over a year ago

Doesn't have to be anonymous. If you have a suitable class, say ProjectSummary, then you change the expression to be ... .Select(r => new ProjectSummary { adGroupsId = r.Key, ....

Klaptrap Over a year ago

Yes, thanks. I have had a disruptive week, what with national holidays and civil unrest, but now have had time in the office to address, thanks.

Collectives™ on Stack Overflow

c# Lambda subqueries (linq2sql)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related