Change sql query to linq in mvc

Question

I want to change sql query to linq using join statement. The query should retrieve columns (days and startdate) while matching records of table 1 to table. In short converting sql query to linq using join statement.

Below is what i've tried.

SQL Query (Working)

SELECT * 
FROM dbo."Batches" 
INNER JOIN dbo.StudentBatchRelation 
      on dbo.Batches.Id = dbo.StudentBatchRelation.BatchId 
WHERE 
      dbo.StudentBatchRelation.StudentId = '3d980306-e36e-4581-8c98-219717cb1668'

LINQ (not fetching result)

var result = (from t1 in contBatch.GetallBatchList()
              join t2 in contStudent.getAllStudentBatchList() 
                   on t1.Id equals t2.batchId where t2.studentId == studentid
              select new { t1.Days, t1.BatchDate }).ToList();

@user100020, try select new {t1, t2} https://stackoverflow.com/questions/32433/creating-a-linq-select-from-multiple-tables — dremerDT, Jan 23 '18 at 07:38
No i don't get any error. I see my query is fine to work, it just that i wasn't calling batchid in getAllStudentBatchList method. — Salar Muhammad, Jan 23 '18 at 08:00
Define proper relations and navigation properties and you won't need any joins. — Panagiotis Kanavos, Jan 23 '18 at 13:06
@user100020 if my answer helps you, please feel free to accept it to close the topic — GGO, Jan 24 '18 at 08:41

score 1 · Accepted Answer · answered Jan 23 '18 at 08:28

If your EF entities are well defined, you could simplify your query with :

var result = Db.Batches
               .Include(p => p.StudentBatchRelation)
               .Where(p => p.StudentBatchRelation.StudentId = "3d980306-e36e-4581-8c98-219717cb1668")
               .ToList();

Otherwise, if you have to use your Getallxxxx functions, you could do :

var result = (from t1 in contBatch.GetallBatchList()
              join t2 in contStudent.getAllStudentBatchList()
                     on t1.Id equals t2.batchId 
              where t2.studentId == "3d980306-e36e-4581-8c98-219717cb1668"
              select t1)
              .ToList();

Your answer is similar to what I've done already as mentioned in my question. — Salar Muhammad, Jan 24 '18 at 08:59

Change sql query to linq in mvc

1 Answers1