convert sql to linq sample

Question

Hello,

I've got a sql statement, but I can't get it working in linq. Can someone show me how I can write the following sql statement as linq?

SELECT * FROM mobileApplication
LEFT JOIN videoMobile ON mobileApplication.id = videoMobile.mobileApplicationId
      AND videoMobile.videoId = 257

It's a left join with a where statement on the right table. It works in sql server 2005, but I'd like to write it in linq.

Answer 1

There is a product that will do this for you. I have found it very useful. The product name is Linqer. It is not free, but not expensive, and offers a 30 day trial. I have found very few queries it is not able to convert. It has worked well for me.

http://www.sqltolinq.com/

Answer 2

Its something like:

from ma in mobiledApplication.DefaultIfEmpty()
join vm in videoMobile on new { mobileApplicationId = ma.id, videoId = 257 } equals new { mobileApplicationId = vm.mobileApplicationId, videoId = vm.videoId } into videoMobileApplication
from vma in videoMobileApplication
select vma

The keys being the default if empty and using anonymous objects on the join criteria to incorporate 257 into the join.

I am pretty sure that using a where clause for the 257 will achieve the same result though...

Answer 3

I didn't verify the syntax, but try this...

var mobileApplications = from ma in mobileApplication
                         join vm in videoMobile on ma.id equals vm.mobileApplicationId into j1
                         from j2 in j1.DefaultIfEmpty()
                         where vm.videoId == 257
                         select ma;

Answer 4

Try some like this:

var query = 
    from m in mobileApplication
    join v in videoMobile 
        on m.id = v.mobileApplicationId and v.id = 257
        select m;

See here:

http://msdn.microsoft.com/en-us/library/bb397676%28v=VS.100%29.aspx

http://msdn.microsoft.com/en-us/magazine/cc163400.aspx