How do I convert this SQL query to LINQ-to-SQL?

Question

How do I convert this SQL query to LINQ-to-SQL?:

select 
    COUNT(ua.UserAlertID) as Alerts, 
    ph.LastName +' '+ ph.MiddleName as Name,
    ph.Email,us.UserSystemID,
    ur.UserStatus from PHUser ph 
inner join UserSystem us on us.UserID=ph.UserID
inner join UserRole  ur on ur.UserID=ph.UserID
inner join Role rr on rr.RoleID=ur.RoleID
inner join UserAlerts ua on ua.SeniorID=ph.UserID
group by ph.LastName,ph.MiddleName,ph.Email,us.UserSystemID,ur.UserStatus 

I have converted most of the above query to LINQ but I got stuck to counting the number of values on the ua.UserAlertID column: COUNT(ua.UserAlertID) as Alerts.

How do I convert that to LINQ?

Kindly suggest How to convert COUNT(ua.UserAlertID) as Alerts in Linq??

Thanks

Answer 1

A number of people can help you with this query. It's pretty straightforward. There is a product (very inexpensive) that can help you with these sorts of things. The product is Linqer (http://www.sqltolinq.com/). This tool will convert most SQL statements to Linq. There is a 30 day trial period and I think the cost is < $40. I have used it many times to get up to speed on converting SQL queries to Linq.

Then I might suggest you get a free product called LinqPad. This will allow you to prototype your Linq queries before pasting them into production code. It is a phenomenal tool.

Answer 2

Can I see your converted LinQ?

If your Query is ok, you got a line like

group s by new {ua.UserAlertId,...,...} into temp.

as I remember in select new {} part use

select new 
{
Total Alerts = temp.Key.UserAlertdId.Count(), ...,...,...,...
}

can you please try this and notify me?