locked
Conversion from SQL Query to Linq Joins RRS feed

  • Question

  • Hi All,
     I am new to LINQ
     I am trying to convert sql query into linq joins
     I have converted it but not getting proper result because of not able to use group by clause.
     Please help

     
    Select sum(m.[hour]*isnull(m.unitcost,0)) as totalcost,f.estimates, 
    	convert(datetime,'01-'+replace(m.month,'','-')) as staffdate from pmaster p 
    	inner join stafffinancials f 
    	on p.projectid=f.projectid 
    	join staffmonth m 
    	on 
    	(f.projectid = m.projectid) and 
    	(f.linenumber = m.linenumber)  and 
    	(f.estimates= m.estimates) and 
    	(f.linestatus<>'d') and  
    	(m.month <>'' or month is not null) and  m.[hour]<> '0.0'
    	and f.linestatus<>'d' and f.projectID=@projectId
    	group by f.estimates,convert(datetime,'01-'+replace(m.month,'','-'))
    
    LINQ Query
    
    from p in pssStaff.PMasters
                                join f in pssStaff.StaffFinancials
                                on
                                new { p.ProjectID }
                                equals
                                new { f.ProjectID }
                                join m in pssStaff.StaffMonths
                                on
                                new { f.ProjectID, f.LineNumber, f.Estimates, LineStatus = (f.linestatus != 'd'), Month = true, Hour = true, ProjectId = (f.ProjectID == projectId) }
                                equals
                                new { m.ProjectID, m.LineNumber, m.Estimates, LineStatus = true, Month = (m.Month != string.Empty || m.Month != null), Hour = (m.Hour != 0.0), ProjectId = true }
                                into finance
                                from vd in finance
                                select new
                                {
                                    TotalCost = vd.Hour.Value * vd.unitcost.Value,
                                    Estimates = f.Estimates,
                                    StaffDate = Convert.ToDateTime(string.Concat("01-", vd.Month.Replace(" ", "-"))),
                                    LineStatus = f.linestatus
                                };
    Now the issue is i am not getting proper result.
    Please help me out how to add group by clause and get the exact result as sql qury does.
    Tuesday, September 8, 2009 11:50 AM

Answers

  • I think that some times, trying to translate everything into LINQ is a bit tooooooo much.
    It can take you quite long to get it right and  it can make the query more difficult to understand. And the most important, complicated queries are sometimes translated into a slower SQL.

    What I've done is to find a solution mid way for those cases, which is execute the SQL directly with LINQ and use the Select to load the object.

    You can do it like this:

    Option A: (you already have a type that matches the values returned )
    var result = DataContext.ExecuteQuery<YourType>( sql , parameters);

    Option B: (you don't have a type and you create one to read the values "DummyType")
    var result = DataContext.ExecuteQuery<DummyType>( sql , parameters).Select(
                                s=> new
                                {
                                     TotalCost = s.Hour * s.UnitCost,
                                     Estimates = s.Estimates,
                                     ....
                                 });

    It may not be 100% LINQ, but LINQ is not a Business Layer either (only helps with the DAL), and the important thing is that you can predict the SQL that will be executed and tune it if required (specially in those complicated queries).

    I hope it helps.

    Tuesday, September 8, 2009 11:06 PM

All replies

  • I think that some times, trying to translate everything into LINQ is a bit tooooooo much.
    It can take you quite long to get it right and  it can make the query more difficult to understand. And the most important, complicated queries are sometimes translated into a slower SQL.

    What I've done is to find a solution mid way for those cases, which is execute the SQL directly with LINQ and use the Select to load the object.

    You can do it like this:

    Option A: (you already have a type that matches the values returned )
    var result = DataContext.ExecuteQuery<YourType>( sql , parameters);

    Option B: (you don't have a type and you create one to read the values "DummyType")
    var result = DataContext.ExecuteQuery<DummyType>( sql , parameters).Select(
                                s=> new
                                {
                                     TotalCost = s.Hour * s.UnitCost,
                                     Estimates = s.Estimates,
                                     ....
                                 });

    It may not be 100% LINQ, but LINQ is not a Business Layer either (only helps with the DAL), and the important thing is that you can predict the SQL that will be executed and tune it if required (specially in those complicated queries).

    I hope it helps.

    Tuesday, September 8, 2009 11:06 PM
  • Thanks for you reply and i used it as SP and called it from LINQ.
    But still i am eager to find the result using LINQ and i will...:)
    Wednesday, September 9, 2009 8:36 AM