How to Convert This SQL Select to Linq Query

Convert SQL to LINQ Query

Have you tried Linqer http://www.sqltolinq.com

An SQL-> LINQ converter..

Or LINQPad
http://www.linqpad.net/

Convert SQL query into Linq expression

1 - SQL : use inner join instead IN :

SELECT SubjectName FROM Subject sub
INNER JOIN SubjectEnrolled subEn on sub.SubjectId = subEn.SubjectId
WHERE subEn.StudentID = 7

2 - Linq Query Join:

var res = (from sub in db.Subjects
          join subEn in db.SubjectEnrolleds on sub.SubjectId equals subEn.SubjectId
          where subEn.StudentID = 7).ToList();

I hope you find this helpful.

Convert SQL query into linq C#

var teacherId = ;  // your teacherId here

var result = new {
    TeacherId = teacherId,
    Subjects = db.TeacherSubjects.Where(ts => ts.TeacherId == teacherId).Select(x => x.SubjectId).Distinct().Count()
};

Or you can group by SubjectId:

var teacherId = ;  // put your teacherId here
var result = 
    (from ts in TeacherSubjects.Where(x => x.TeacherId == teacherId)
    group ts by ts.SubjectId into gr
    select new 
    {       
        TeacherId = teacherId,
        Subjects = gr.Count()
    }).ToList();

Converting SQL query to LINQ or LINQ fluent Syntax

Assuming the District has a collection navigation property to ServiceRecord as it should, e.g. something like

public virtual ICollection<ServiceRecord> ServiceRecords { get; set; }

you can avoid the GroupBy by simply starting the query from District and use simple projection Select following the navigations:

var query = _dbContext.Districts
    .Select(d => new DistrictClientsLookUpModel
    {
        DistrictName = d.Name,
        ClientsServedCount = d.ServiceRecords
            .Where(s => s.CreatedAtUtc >= startUniversalTime && s.CreatedAtUtc <= endUniversalTime)
            .SelectMany(s => s.NsepServiceRecords)
            .Select(r => r.ClientRegNo).Distinct().Count()
    });

How to convert my sql query to linq query

Try the following, not tested; assumes a database connection db:

var infoQuery =
    (from cal1 in db.Calisanlar
    where cal1.Lisans == 0
    select cal1.CalisanId)
    .Intersect
        (from cal2 in db.Covids
        select cal2.CalisanId);
var result = inforQuery?.ToList() ?? 0;

or, lambda expression

var infoQuery = 
    (db.Calisanlar.Where(x => x.Lisans == 0).Select(x => x.CalisanId))
    .Instersect(db.Covids.Select(x => x.CalisanId))

UPDATE
Assuming CalisanID is an integer checks for null return and returns zero if found. Again, not tested.

---

Related Topics