Convert stored procedure to LINQ

Question

I'm using MVC3 and still learning LINQ. I'm having some trouble trying to convert a query to LINQ to Entities. I want to return an Json method

My stored procedure

Create Procedure [dbo].[ResourceReports]
(
    @EmployeeID int
) 
as
begin
    select   p.projectName AS Projects,  count( b.[CreatedByID]) AS Bugs
    from [EmployeeDetails] e inner join [Bugs] b  on e.[EmployeId] = b.[CreatedByID]
    inner join Projects p on b.ProjectId = p.ProjectId
    where e.[EmployeId] = @EmployeeID   
    group by P.projectName
end

What I have is a few tables, I started writing this out in LINQ but I'm not sure how to properly return the correct type or cast this.

My controller

public JsonResult Getchart()
{
    var Bug = db.Bugs.ToList<Bug>();
    var EmployeDetails = db.EmployeeDetails.ToList<EmployeeDetail>();
    var projects = db.Projects.ToList<Project>();

    var result = (from e in EmployeDetails 
                  join b in Bug on e.EmployeId equals b.CreatedByID
                  join p in projects on b.ProjectId equals p.ProjectId
                  where e.EmployeId = @EmployeId
                  group p.projectName
                  select new (p.projectName as Project ,count(b.CreatedByID) as Bug)).Take(50);

    return Json(result,JsonRequestBehavior.AllowGet);
}

How will I pass the parameter to for the query, want the data to be returned in json format.

D Stanley · Answer 1 · 2012-08-23T13:47:49.183

Assuming you can pass the value in as a parameter to the method:

public JsonResult Getchart(int employeeId)
{
    var Bug = db.Bugs.ToList<Bug>();
    var EmployeeDetails = db.EmployeeDetails.ToList<EmployeeDetail>();
    var projects = db.Projects.ToList<Project>();

    var result = (from e in EmployeeDetails 
                  join b in Bug on e.EmployeeId equals b.CreatedByID
                  join p in projects on b.ProjectId equals p.ProjectId
                  where e.EmployeeId == employeeId   // <-- use the parameter here
                  group p by p.projectName into g
                  select new {
                     Project = g.Key,
                     Bug = g.Count() 
                     }
                 ).Take(50);
    return Json(result,JsonRequestBehavior.AllowGet);
}

BTW I intentionally corrected a few spellings of Employee

@RaghuBandaru - please see the slight syntax change on the group statement. — D Stanley, Aug 23 '12 at 13:31

Sako73 · Answer 2 · 2012-08-23T20:49:18.867

Is this what you need:

public JsonResult Getchart(int employeId)
  {
        var Bug = db.Bugs.ToList<Bug>();
        var EmployeDetails = db.EmployeeDetails.ToList<EmployeeDetail>();
        var projects = db.Projects.ToList<Project>();

       var result =   (from e in EmployeDetails 
                      join b in Bug on e.EmployeId equals b.CreatedByID
                      join p in projects on b.ProjectId equals p.ProjectId
                      where e.EmployeId == employeeId
                      group p.projectName
                     select new (p.projectName as Project ,count(b.CreatedByID) as Bug)).Take(50);
                       return Json(result,JsonRequestBehavior.AllowGet);
     }

Are you sure you want to do all of those "ToList<>()" calls? Once you call "ToList<>()", you bring all three of those tables into memory from the database. If they are large, that could be a performance issue.

@Sako73..Thank u geven answer – Dotnet ReSource Aug 23 '12 at 13:27 — Dotnet ReSource, Aug 23 '12 at 13:27

James · Answer 3 · 2012-08-23T14:44:49.850

1

If this is a controller action, you would probably want to pass the ID via the URL. Also, there is no need to call ToList on your tables before querying, do the query at the database and only pull down the results e.g.

public JsonResult GetChart(int employeeId)
{
    var query = (from e in db.EmployeeDetails
                join b in db.Bugs on e.EmployeeId equals b.CreatedById
                join p in db.Projects on b.ProjectId equals p.ProjectId
                where e.EmployeeId == employeeId
                group new {p, b} by new {
                    p.ProjectName
                } into g
                select new {
                    Project = g.Key.Name,
                    Bugs = g.Count()
                }).Take(50);
    return Json(query.ToList(), JsonRequestBehaviour.AllowGet);
}

edited Aug 23 '12 at 14:44

answered Aug 23 '12 at 13:30

James

80,725
18
167
237

@RaghuBandaru change it to `equals` keyword instead – James Aug 23 '12 at 13:46
.yes equals taken but still am getting error JsonRequestBehaviour and select sentence.. – Dotnet ReSource Aug 23 '12 at 13:48
`still getting error` - I can't help you unless you post the error (I haven't tested the code so their could potentially be syntax errors). – James Aug 23 '12 at 13:50
@RaghuBandaru I will...if you can tell me the error you are getting? – James Aug 23 '12 at 13:52
...in Select Statment error("only assignment call increment decrement and new object Expression can be use as a statment" and JsonRequestBehaviour("does not exist current") – Dotnet ReSource Aug 23 '12 at 13:57
See updated answer, it was missing an `into g` call after group statement. – James Aug 23 '12 at 13:59
@James... Am getting Runtime Error in Return Statemnet ("Unable to create a constant value of type 'BugTrackerReportcharts.Models.Project'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.") – Dotnet ReSource Aug 23 '12 at 14:27
@RaghuBandaru in the `select` part try changing `Project = ...` to `ProjectName = ...` and if you get the same error for `Bugs = ...` change that to `BugCount = ...`. – James Aug 23 '12 at 14:28
Just noticed the line `join p in projects on b.ProjectId...` should be `join p in db.Projects on b.ProjectId...`. Try that. – James Aug 23 '12 at 14:44
@James...If you dont mind ..am not getting database values whenever i check in debug in Result View ("Expanding the result view will enumarate the IEnumarable") in Result vie method in came... – Dotnet ReSource Aug 23 '12 at 14:53
So the query is executing you just aren't getting any results now? – James Aug 23 '12 at 14:59
@james...this query used result show the Gridview ...but client requeremnt in Bind the Pie chat so thats why am doing this type – Dotnet ReSource Aug 23 '12 at 15:03
@RaghuBandaru I am afraid binding the query to a Pie chart is beyond the scope of your original question. You should ask a different question for that. – James Aug 23 '12 at 15:09
@james... Thank u for helping me ..and response me.ok no issues i will do this work – Dotnet ReSource Aug 23 '12 at 15:12
@RaghuBandaru no problem, if you are looking for a good charting library you should check out the [Google Chart Tools](https://developers.google.com/chart/). – James Aug 23 '12 at 15:20

score 0 · Accepted Answer · answered Aug 24 '12 at 09:24

public JsonResult GetChart()
            {
                //int employeeId
              var Bug = db.Bugs.ToList<Bug>();
              var EmployeDetails = db.EmployeeDetails.ToList<EmployeeDetail>();
              var projects = db.Projects.ToList<Project>();

              var query = (from e in EmployeDetails
                           join b in Bug on e.EmployeId equals b.CreatedByID
                           join p in projects on b.ProjectId equals p.ProjectId
                           where e.EmployeId == 1
                           group new { p, b } by new
                           {
                               p.projectName
                           } into g
                           select new ChartModel
                           {
                               ProjectName = g.Key.projectName,                     

                               bug = g.Count()
                           }).ToList();
              return Json(query, JsonRequestBehavior.AllowGet);
}

i Got ...

You shouldn't post a new answer for this considering you have already accepted an answer, update your original question. — James, Aug 24 '12 at 13:47
@james.. I had bind the pie chart .now i will bind the BarChart In View Format in littile bit confuse .can u help me — Dotnet ReSource, Aug 24 '12 at 13:55

Convert stored procedure to LINQ

4 Answers4