Question 86420 in ASP.NET Question posted in
The official documentation can be found here.

Linq To Sql Convert – Asp.net

I have query in linq and I want to convert that query in sql I am not very good in linq so can anyone help me to convert this query in sql:
We have ticket table and issues table ticket id is referenced in issue table :

var tickets = tickets.Where(x => x.Issues.Where(y => y.ResolutionDetails == "Resolved" &&
                                y.ResolvedwithSupplierDate == null).Count() == x.Issues.Count() && x.Issues.Count() > 0).ToList();
Tags:

3

Answers


  1. 0

    You can try using this tool – https://www.linqpad.net/. It may not be straightforward without setting up your data but perhaps may help. I think that changing the EF core logging levels may also help to see the SQL generated. (https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/simple-logging)
    This is not an answer as such but just some suggestions. Hope it helps.

    Login or Signup to reply.
  2. 0

    I hope this rewritten query will help you to understand what this query do:

    var tickets = tickets.Where(x => 
        x.Issues.All(y => y.ResolutionDetails == "Resolved" 
           && y.ResolvedwithSupplierDate == null) 
        && x.Issues.Any()
    ).ToList();
    Login or Signup to reply.
  3. 0

    It is trying to get the ticket where the resolution of the ticket is not equal to 0 and the ticket resolution count is equal to resolved count.
    it uses the resolved as resolved tag. If it is not resolved then the resolvewithsupplier date would be null

    Sql should be something like

    select * from tickets where (select count(Id) from Issues where TicketId = Ticket.Id <> 0
and ((select count(Id) from Issues Where TicketId = Ticket.Id) = (select count(Id) from Issues 
Where TicketId and Ticket.Id = 'Resolved' and ResolvedwithSupplierDate is null)))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search