How to convert my sql query to linq query

Question

Hi i write this sql query but i want to write linq version sql version

Select CalisanId From Calisanlar Where Lisans=0 INTERSECT (Select CalisanId From Covids)

  

        public List<LisansCovid> GetLisansCovid()
            {
                using (SirketDBContext context = new SirketDBContext())
                {
                    var result =
        (from cal1 in context.Calisanlar
         where cal1.Lisans == 0
         select cal1.CalisanId)
        .Intersect
            (from cal2 in context.Covids
             select cal2.CalisanId);
    
     //exception              return result.ToList();        
            }}

Does this answer your question? [Linq to SQL and Intersect](https://stackoverflow.com/questions/3937693/linq-to-sql-and-intersect) — Peter Smith, Dec 17 '21 at 16:06
What LINQ are you using: LINQ to Objects / SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x / 6.x? What database provider? — NetMage, Dec 17 '21 at 22:28
i develop web app on visual studio 2019 and i want to display sql query on swagger ui @NetMage — habudaney, Dec 18 '21 at 08:27
Sample data and the data structures you are using would be a big help. The error message is saying that the query is returning null, hence the `ToList()` fails. — Peter Smith, Dec 18 '21 at 09:15
very thanks @PeterSmith :)) can you also look at this problem ? https://stackoverflow.com/questions/70402378/how-to-this-sql-queries-convert-to-linq — habudaney, Dec 18 '21 at 09:57
I'm not a code writing factory. Stack Overflow is here to help with diagnosing and solving problems; to assist with learning. — Peter Smith, Dec 18 '21 at 10:00

Peter Smith · Accepted Answer · 2021-12-18T09:30:13.920

0

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.

edited Dec 18 '21 at 09:30

answered Dec 17 '21 at 20:52

Peter Smith

5,528
8
51
77

firstly thanks but i didn't work when i write return infoQuery.ToList();. I edited my code. you can see – habudaney Dec 18 '21 at 08:21
Answer updated to allow for null return. However, still need sample data to know why the query is returning a null. – Peter Smith Dec 18 '21 at 09:31

How to convert my sql query to linq query

1 Answers1