CODEWITHSUNDEEP
c#sqllinq
Salar Muhammad
Salar Muhammad

Reputation: 334

Change sql query to linq in mvc

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();

Upvotes: 0

Views: 298

Answers (1)

GGO
GGO

Reputation: 2748

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();

Upvotes: 1

Related Questions