Trying to convert sql query to linq to sql

Question

I am running into problem with converting sql from stored procedure to the linq to sql code. The query basically groups items and returns sum for the number value.

The code I have been trying to make is executes, but it doesn't group items and the sum doesn't work either. I just get values like if I would get without any group by and without sum.

It's also seems like it has no regards to the organization I want it to select from.

Sql code is works, the linq to sql conversion is the one that makes problems.

I would appreciate if anyone can point me with what I am doing wrong.

Sql code:

CREATE TABLE #T1 (a varchar(255), b varchar(255), c varchar(250), d varchar(250), e varchar(250), ACAP int);



INSERT INTO #T1
Select SubProgram AS a, SubSubProgram as b, SubSubSubProgram as c, Sub4Program as d, Su5Program as e, Sum(Deliverable1) AS ACAP
 FROM LogicModel.Deliverables, LogicModel.ProgramNumbers
 WHERE 
    LogicModel.ProgramNumbers.OrganizationName = @OrganizationName AND
    LogicModel.ProgramNumbers.ProgramNumber = LogicModel.Deliverables.ProgramNumber AND
    LogicModel.Deliverables.FiscalYear = @FiscalYear AND
    LogicModel.Deliverables.Program = (09-10.1a) 'Education presentations by type'
GROUP BY
    SubProgram, SubSubProgram, SubSubSubProgram , Sub4Program, Sub5Program


SELECT a, b, c, d, e, isnull(ACAP, 0) as ACAP from #T1

Linq to Sql

  var deliverables = (from de in OCHART.Deliverables
                                join pn in OCHART.ProgramNumbers on de.ProgramNumber equals pn.ProgramNumber1 into prt
                                from x in prt.Where(prt2 => prt2.OrganizationName.Equals(organization)).DefaultIfEmpty()
                                where de.FiscalYear == FiscalYear && de.Program.Equals("(09-10.1a) Education presentations by type")
                                group de by new { SubProgram = de.SubProgram, SubSubProgram = de.SubSubProgram, SubSubSubProgram = de.SubSubSubProgram, Sub4Program = de.Sub4Program, Sub5Program = de.Sub5Program, deliverable = de.Deliverable1 } into gr
                                select new
                                {
                                    SubProgram = gr.Key.SubProgram,
                                    SubSubProgram = gr.Key.SubSubProgram,
                                    SubSubSubProgram = gr.Key.SubSubSubProgram,
                                    Sub4Program = gr.Key.Sub4Program,
                                    Sub5Program = gr.Key.Sub5Program,
                                    deliverable1 = gr.Sum(g => g.Deliverable1),
                                    deliverable2 = gr.Sum(g => g.Deliverable2)
                                }).ToList();

Thanks for taking time and trying to help. I appreciate it.

Answer 1

Quick solution(WorkAround):

Why don't you run the stored procedure you have. Since your stored procedure is already tested. It would save your time.

Just drag and drop the stored procedure to your DBML file. All you have to do is use it.