How can I convert this SQL query to Linq with C#

Question

Since I am new to Linq so not able to convert this SQL query to Linq I am pasting my code that I have already tried, it gives null records.

Thanks in advance.

This is my Linq C# Statements that I have tried:

from ts in Db.Tasks

join prt in Db.ProjectTasks on ts.Id equals prt.TaskId into PojTsk
from t1 in PojTsk 

join TL in Db.Timeline on ts.Id equals TL.TypeId into Tmln
from t2 in Tmln

join DUR in Db.Duration on ts.Id equals DUR.TypeId into Dur
from t3 in Tmln

where t1.ProjectId == ProjectId 
&& t2.Type == (int)Provider.EntityType.TASK 
&& t3.Type == (int)Provider.EntityType.TASK
select ts

This is my SQL query that I am trying to convert to Linq with C#:

SELECT 
    CONCAT('T', R1.Id)  as Id, 
    R1.Name, R1.Description, R1.Priority, 
    R1.Stage, R1.Status, R1.CreatorId, 
    R2.ProjectId, R3.StartDate, R3.EndDate, 
    R3.LatestEndDate, R3.LatestStartDate, 
    R3.EarliestStartDate, R3.ActualStart, R3.ActualEnd, 
    R3.RemTime, R3.ReshowDate, R3.RemTime, R3.Completed, 
    R4.ActualDuration, R4.ActualDurationPlanned      
FROM 
    (SELECT * 
     FROM [ProjectManagement].[dbo].[Tasks] AS TS) AS R1
JOIN
    (SELECT * 
     FROM [ProjectManagement].[dbo].[ProjectTasks]) AS R2 ON R1.Id = R2.TaskId
LEFT JOIN
    (SELECT * FROM [ProjectManagement].[dbo].[Timelines] 
     WHERE Type = 3) AS R3 ON R1.Id = R3.TypeId
LEFT JOIN
    (SELECT * FROM [ProjectManagement].[dbo].[Durations] 
     WHERE Type = 3) AS R4 ON R1.Id = R4.TypeId
WHERE
    ProjectId = 1

My sql query result in SQL Server Management Studio

See http://stackoverflow.com/questions/19356439/left-join-in-linq-to-entities — Henrik Ilgen, Jun 10 '16 at 07:53
@Mahesh Malpani, how can you say that "there is no syntax of select * from" I have tested this query on my SQL Server Management Studio — Sankhat Vijay, Jun 10 '16 at 08:07
You now asking about specific library. Not sure about that but you can use entity framework and .net linq instead of that — Mahesh Malpani, Jun 10 '16 at 08:25
Those who have down voted my question do they answer my question? :-) — Sankhat Vijay, Jun 10 '16 at 10:19
What is `Db`? In other words, what type of LINQ are you using? Further, show the class model. This is all so much easier with navigation properties. You probably don't need one single LINQ join statement. — Gert Arnold, Jun 10 '16 at 21:49

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Even still unable to determine which query should be executed first, I had figured out query execution steps:

(1) Inner join Tasks table with ProjectTasks table where ProjectId = 1,

(2) Left join (1) to Timelines table where Type = 3,

(3) Left join (2) to Durations table where Type = 3.

My nearest approach on given SQL query with LINQ (I have neither test into LINQPad nor directly to VS yet):

// first inner join
var query = (from R1 in Tasks
join R2 in ProjectTasks on R1.Id equals R2.TaskId into Group1

// second left join
from T1 in Group1.DefaultIfEmpty() 
join R3 in (from t in Timelines where t.Type == 3 select t) on T1.Id equals R3.TypeId into Group2

// third left join
from T2 in Group2.DefaultIfEmpty()
join R4 in (from d in Durations where d.Type == 3 select d) on T2.Id equals R4.TypeId

// still not sure to determine where condition here...
where T2.ProjectId == 1 // join result condition

select new {
    Id = T1.Id.ToString().Concat("T"), // assume Tasks.Id is an int identity column converted to String value
    Name = T1.Name,
    Description = T1.Description,
    Priority = T1.Priority,
    Stage = T1.Stage,
    Status = T1.Status,
    CreatorId = T1.CreatorId,
    ProjectId = T1.ProjectId,
    StartDate = T2.StartDate,
    EndDate = T2.EndDate,
    LatestEndDate = T2.LatestEndDate,
    EarliestStartDate = T2.EarliestStartDate,
    ActualStart = T2.ActualStart,
    ActualEnd = T2.ActualEnd,
    RemTime = T2.RemTime,
    ReshowDate = T2.ReshowDate,
    Completed = T2.Completed,
    ActualDuration = R4.ActualDuration,
    ActualDurationPlanned = R4.ActualDurationPlanned
}).ToList();

CMIIW.

Thanks for your kind replay, but result is still null. – Sankhat Vijay Jun 10 '16 at 09:34 — Sankhat Vijay, Jun 10 '16 at 09:34

Sphinxxx · Answer 2 · 2016-06-10T10:52:10.483

0

Try this:

from ts  in Db.Tasks
join prt in Db.ProjectTasks on ts.Id equals prt.TaskId
from tl  in Db.Timeline.Where(x => (x.TypeId == ts.Id)
                                && (x.Type == (int)Provider.EntityType.TASK))
                       .DefaultIfEmpty()
from dur in Db.Duration.Where(x => (x.TypeId == ts.Id)
                                && (x.Type == (int)Provider.EntityType.TASK))
                       .DefaultIfEmpty()

where  (prt.ProjectId == ProjectId)

select new {
    Id = "T" + ts.Id,
    ts.Name,
    ts.Description,
    //...

    prt.ProjectId,

    tl.StartDate,
    tl.EndDate,
    tl.LatestEndDate,
    //...

    dur.ActualDuration,
    dur.ActualDurationPlanned
}

from x in y.DefaultIfEmpty() is a simple way to create a LEFT JOIN.

EDIT: Changed .Where() in left joins.

edited Jun 10 '16 at 10:52

answered Jun 10 '16 at 09:29

Sphinxxx

12,484
4
54
84

Result is null still. – Sankhat Vijay Jun 10 '16 at 09:50
I tried your Linq and also changed where condition but no luck (from ts in Db.Tasks join prt in Db.ProjectTasks on ts.Id equals prt.TaskId from tl in Db.Timeline.Where(x => x.TypeId == ts.Id && x.Type == 3) from dur in Db.Duration.Where(x => x.TypeId == ts.Id && x.Type == 3) where (prt.ProjectId == ProjectId) – Sankhat Vijay Jun 10 '16 at 09:51
See this answer to log the SQL generated by EF: http://stackoverflow.com/a/20751723/1869660 Then it's easier to see what's wrong. (Note: I updated my answer with you corrected where conditions) – Sphinxxx Jun 10 '16 at 10:56
Ok, but find the SQL that EF generates (see the link in my comment). Then it will be easier to see what's wrong. – Sphinxxx Jun 10 '16 at 12:06
http://pastie.org/10871686 I am getting this query. by loging, can u please help, I think it's inner join not left join – Sankhat Vijay Jun 10 '16 at 13:10
You're right, those are inner joins. If you're using `.DefaultIfEmpty()`, that's strange. See this similar question, which have answers with a few different syntaxes: http://stackoverflow.com/questions/21748425/how-to-use-left-join-in-entity-framework-query – Sphinxxx Jun 10 '16 at 14:14

How can I convert this SQL query to Linq with C#

2 Answers2