SQL into LINQ to Entities

Question

I have a big problem.

I'm for last 5 year SQL-boy, but now I need to convert my SQL query into LINQ to entity C# format. Because I'm new in LINQ (complex statements) right now I need fast help.

Thank's in advance.

P.S. also I need some advices, some start point to start rapidly to learn LINQ to entities.

Here is my SQL (direct from my app(@endDate,@endDate and @glChartID remain as parameters also in my c# app)):

SELECT budget.accountid,   
budget.perioddate,
budget.lastyear,
budget.thisyear,   
budget.budget,   
budget.operplan,
budget.forecast,   
glchart.accounttype,
glchart.headertype

FROM budget INNER JOIN glchart ON budget.accountid = glchart.id
WHERE budget.accountid = @glChartID AND budget.perioddate BETWEEN @startDate and @endDate AND glchart.headertype NOT LIKE 'Header%'

UNION

SELECT  glchart.id,   
budget.perioddate,   
SUM(ISNULL(budget.lastyear, 0)),   
SUM(ISNULL(budget.thisyear, 0)),    
SUM(ISNULL(budget.budget, 0)),    
SUM(ISNULL(budget.operplan, 0)),  
SUM(ISNULL(budget.forecast, 0)),  
glchart.accounttype,
glchart.headertype

FROM budget INNER JOIN glchart ON budget.accountid = glchart.id
WHERE budget.accountid  
IN 
(SELECT g.id FROM glchart g
WHERE g.code >= glchart.code AND g.code <  

CASE
WHEN glchart. headerlevel = 1 AND
(SELECT MAX(g3.code)
FROM glchart g3
WHERE g3.headerlevel = 1
) = glchart.code
THEN 
(SELECT MAX(g2.code)
FROM glchart g2
WHERE g2.code >= g.code)
ELSE
(SELECT MIN(g2.code)
FROM glchart g2
WHERE g2.code > glchart.code AND
g2.headerlevel  = glchart. headerlevel) END ) AND
glchart.id = @glChartID AND
budget.perioddate BETWEEN @startDate AND @endDate AND
glchart.headertype LIKE 'Header%'

GROUP BY glchart.id, budget.perioddate, glchart.accounttype, glchart.headertype

Until today, I managed (thanks to DOK)how to do it and this is how my LINQ is look like right now:

var query = ((ObjectQuery<Budget>)(                

                            from budgets in this.ObjectContext.Budgets

                            join glcharts in this.ObjectContext.GLCharts on new { AccountID = budgets.AccountID } equals new { AccountID = glcharts.ID }
                            where
                                    (!(from glC in this.ObjectContext.GLCharts
                                     where Convert.ToInt16(glC.Code) >= Convert.ToInt16(glcharts.Code) && glC.Code != (Convert.ToInt64(glcharts.HeaderLevel) == 1 &&

                                         (from g3 in this.ObjectContext.GLCharts
                                             where  Convert.ToInt64(g3.HeaderLevel) == 1
                                              select new {g3.Code}).Max(p => p.Code) == glcharts.Code ? 
                                                (from g2 in this.ObjectContext.GLCharts
                                                    where Convert.ToInt16(g2.Code) >= Convert.ToInt16(glC.Code)
                                                      select new {g2.Code}).Max(p => p.Code) : 
                                                (from g2 in this.ObjectContext.GLCharts
                                                     where Convert.ToInt16(g2.Code) > Convert.ToInt16(glcharts.Code) && g2.HeaderLevel == glcharts.HeaderLevel
                                                      select new {g2.Code}).Min(p => p.Code))
                                    select new {glC.ID}

                                 ).Contains(new { budgets.AccountID }) &&  

                                 glcharts.ID == 2376 && budgets.PeriodDate >= StartDate && 
                                 budgets.PeriodDate <= EndDate && 
                                 glcharts.HeaderType.StartsWith("Header"))

                                 ).Contains(new { budgets.AccountID }) &&  glcharts.ID == 2376 && budgets.PeriodDate >= StartDate && budgets.PeriodDate <= EndDate && glcharts.HeaderType.StartsWith("Header")

                                   group new {glc = glcharts, b = budgets} 
                                    by new {
                                     glcharts.ID,
                                     budgets.PeriodDate,
                                     glcharts.AccountType,
                                     glcharts.HeaderType
                                    } into g

                            select new {
                              AccountID = (System.Int32?)g.Key.ID,
                              PeriodDate = (System.DateTime?)g.Key.PeriodDate,
                              LastYear = g.Sum(p => ((System.Decimal?)p.t.LastYear ?? (System.Decimal?)0)),
                              ThisYear = g.Sum(p => ((System.Decimal?)p.t.ThisYear ?? (System.Decimal?)0)),
                              Budget = g.Sum(p => ((int?)p.t.Budget1 ?? (int?)0)), 
                              OperPlan = g.Sum(p => ((System.Decimal?)p.t.OperPlan ?? (System.Decimal?)0)),
                              Forecast = g.Sum(p => ((System.Decimal?)p.t.Forecast ?? (System.Decimal?)0)),
                              AccountType = g.Key.AccountType,
                              HeaderType = g.Key.HeaderType
                                        }));

        return query;

But in THIS LINE: .Contains(new { budgets.AccountID }) I'm getting next error :

Error 8'System.Linq.IQueryable' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.ParallelEnumerable.Contains(System.Linq.ParallelQuery, TSource)' has some invalid arguments

Does anybody have an idea where I'm wrong?

Thanks to everyone.

Answer 1

You might find some help in this excellent reference site.

That will lead you to, for example, two examples for UNION.

If you really must start out at this level of difficulty, you might consider breaking your SQL down into pieces and getting them working bit by bit. Do the first SELECT without the JOIN or WHERE, then add those one at a time. Then do the second SELECT the same way. Then add the UNION.

By the time you get this one worked out, SQL-boy, you will definitely be LINQ-man!