CODEWITHSUNDEEP
sqllinq-to-sqlleft-join
Jeroen Breuer
Jeroen Breuer

Reputation: 263

convert sql to linq sample

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.

Upvotes: 1

Views: 170

Answers (4)

Andersson Melo
Andersson Melo

Reputation: 764

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

Upvotes: 0

Rebecca Chernoff
Rebecca Chernoff

Reputation: 22605

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;

Upvotes: 2

Peter Willis
Peter Willis

Reputation: 956

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...

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48432

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/

Upvotes: 0

Related Questions