Convert sql group by queryto linq

Question

please help to convert sql query to linq,

select top(1) AgentID 
from Leads l 
inner join LeadDetails ld on l.LeadID = ld.LeadID 
where ld.PropertyShown = 0 
group by AgentID 
order by COUNT(AgentID)

is there any tool to convert?

Answer 1

LINQPad will convert from LINQ to SQL, but not the other way.

This question linqpad-convert-sql-to-linq also asks how to convert from SQL to LINQ syntax.

Here is how I did it manually; I typed this into LINQPad to test it -

// Test data
    var Leads = new []{new {LeadID=1,AgentID="John"},new {LeadID=2,AgentID="Jane"}};
    var LeadDetails = new []{new {LeadDetailId=1,LeadID=1,PropertyShown=0},new {LeadDetailId=2,LeadID=1,PropertyShown=0},new {LeadDetailId=2,LeadID=2,PropertyShown=0}};

var topAgentId =
(from l in Leads
join ld in LeadDetails on l.LeadID equals ld.LeadID
where ld.PropertyShown == 0
group l by l.AgentID into agentGroup
orderby agentGroup.Count() ascending // or descending if you want the agent with the most leads
select agentGroup.Key).FirstOrDefault();

topAgentId.Dump();