MVC Displaying the results from a SQL Select Statment
===

Right problem I've been trying to sort for ages now!

I'm trying to display the result from a SQL select command and display this information to my view. I can't simple use EntityFramework, not from what I can see anyway, to do the command because I am bringing in data from 2 different tables and displaying as one, this is for a report.

this is the sql command I need to run.

    select FirstName, LastName,
    (select count(*) from Orders o where U.userID = o.CreatedByUserID and ProductID = 1) as ProductCount
    from Users U
    order by UserID

Is there anything I can do to run this command? I'd be willing to try a EntityFramwork way of doing it if I can't run the SQL directly.

Thanks in advance for any help!