none
Sql to linq sample RRS feed

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

    Thursday, April 15, 2010 2:57 PM

Answers

  •  

    You are correct. I'm not using Linq-to-SQL, but Linq-to-LLBLGen, but the syntax should be the same. NodeEntity.MetaData is a static property which points to Project.DB.Linq.LinqMetaData. Should I ask this question on the LLBLGen forum?


    Yes and no, although the syntax should be the same it depends on if the underlying provider has implemented support for that and how the underlying provider translates to SQL.

    For LLBLGen you may want to try something like:

    from ma in MetaData.MobileApplication
    join vm in MetaData.VideoMobile on new { ma.ID, videoID } equals { vm.MobileApplicationId, vm.VideoId } into v
    from vm in v.DefaultIfEmpty()
    select new { ma, vm }

     

     


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Tuesday, April 20, 2010 9:14 AM
    Answerer

All replies

  • using(var dc = new DataContext())

    {

    var result = dc.VideoMobile.Where(x => x.videoId = 257).SelectMany(c => c.MobileApplication);

    }

    I guess you have a working datamodel which contains the necessary relations.

    Something like that, right out of my head :) I can help you more if needed, feel free to ask :)
    Thursday, April 15, 2010 8:48 PM
  • Thank you for this example. Do you also have a syntax example? Like 'from ma in mobileApplication'.
    Friday, April 16, 2010 6:25 AM
  • Then it would be like this:

    using (var dc = new DataContext())
    {
    var result = from ma in dc.mobileApplication
       from v in dc.videoMobile
        where v.videoId == 257
    select new{ma.mobileApplication, v.videoId}; //<- etc :)
      }

    Friday, April 16, 2010 7:08 AM
  • Unfortunately this doesn´t give back the results I would like. Here is an example of the expected results:

    Existing videoId (260)

    Id

    Name

    Id

    videoed

    mobileApplicationId

    videoHigh

    videoLow

    1

    iPhone

    NULL

    260

    1

    Sneak1.flv

    Sneak1Low.flv

    2

    Blackberry

    NULL

    260

    2

    Sneak2.flv

    Sneak2Low.flv

    NOT existing videoId (257)

    Id

    Name

    Id

    videoed

    mobileApplicationId

    videoHigh

    videoLow

    1

    iPhone

    NULL

    NULL

    NULL

    NULL

    NULL

    2

    Blackberry

    NULL

    NULL

    NULL

    NULL

    NULL

    The above sql query I wrote gives these results back, but the linq query only returns results with an existing videoId. With an id which doesn't exit I don't get any results back.
    Friday, April 16, 2010 7:34 AM
  • Try:

    from ma in dc.MobileApplication
    from v in (
     from vm in dc.VideoMobile
     where vm.MobileApplicationID == ma.ID
       && vm.VideoID == 257
     ).DefaultIfEmpty()
    select new { ma, v }


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

    Friday, April 16, 2010 8:25 AM
    Answerer
  • Thanks for the sample Kristofer, but the query seems to be incomplete. You miss a select at the 'from v in ()' part. This is what I made of your query:

      var q =
        (
          from ma in NodeEntity.MetaData.MobileApplication
          from v in 
            (
              from vm in NodeEntity.MetaData.VideoMobile  
              where vm.MobileApplicationId == ma.Id
                && vm.VideoId == videoId
              select vm
            ).DefaultIfEmpty() 
            select new { ma, v }
        );

    Now I get the following exception: A DefaultIfEmpty() call was found on an entity typed sequence as one side of a join. However, the behavior of the DefaultIfEmpty can't be converted to SQL due to the lack of a predicate or filter to combine left side with right side. 

     

    Friday, April 16, 2010 8:30 AM
  • ahh sorry :)

     

    Do like this:

    using(var dc = new DataConnection())
    {
    from ma in mobileApplications
    from vm in videoApplications.Where(c => c.videoId == ma.videoId && c.VideoId == 257).DefaultIfEmpty()
    select new{ma.Name, vm.VideoId } //
    
    }

    It's kindy strange to do a left join and then limit the left join with an id, but sometimes requirements can seem perculiar :)

     

    Friday, April 16, 2010 9:28 AM
  • Thank you Janus for helping me so much, but it still doesn't work. I updated the query because the mobileApplication doesn't have a VideoId property. This is how it looks like now:

     

      var result =
        (
          from ma in NodeEntity.MetaData.MobileApplication
          from v in NodeEntity.MetaData.VideoMobile.Where(c => c.MobileApplicationId == ma.Id && c.VideoId == videoId).DefaultIfEmpty()
          select new { ma.Id, v.VideoId }
        );

     

    Here are my 2 tables:

    MobileApplication:

    Id

    Int

    Name

    Nvarchar(255)

    VideoMobile:

    id

    int

    videoId

    int

    mobileApplicationId

    int

    fullVideoHigh

    nvarchar(255)

    fullVideoLow

    nvarchar(255)

    Now I've got a situation were MobileApplication is always filled, but VideoMobile might not be. I always want to get the rows from MobileApplication, but if VideoMobile doesn't exist I just want to get null back (a left join), but if the VideoModule does exist (with the where check) I want to get the data from that table aswell (like the example in one of my previous posts).

    Currently I still get the following exception:

    A DefaultIfEmpty() call was found on an entity typed sequence as one side of a join. However, the behavior of the DefaultIfEmpty can't be converted to SQL due to the lack of a predicate or filter to combine left side with right side. 

    Friday, April 16, 2010 9:46 AM
  • Doesn't anybody know how I can convert the following sql to a proper linq statement?

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

    Monday, April 19, 2010 8:00 AM
  • Thanks for the sample Kristofer, but the query seems to be incomplete. You miss a select at the 'from v in ()' part. This is what I made of your query:

     

     var q =
    
     (
    
      from ma in NodeEntity.MetaData.MobileApplication
    
      from v in 
    
      (
    
       from vm in NodeEntity.MetaData.VideoMobile 
    
       where vm.MobileApplicationId == ma.Id
    
       && vm.VideoId == videoId
    
       select vm
    
      ).DefaultIfEmpty() 
    
      select new { ma, v }
    
     );
    
    

     

    Now I get the following exception: A DefaultIfEmpty() call was found on an entity typed sequence as one side of a join. However, the behavior of the DefaultIfEmpty can't be converted to SQL due to the lack of a predicate or filter to combine left side with right side. 

     


    Sorry, yes, I forgot the select in the inner query in my sample. That aside, your corrected version above should work if you use Linq-to-SQL.

    Are you using Linq-to-SQL or Linq-to-something-else? (L2E/EF maybe?)

    ...or rephrased, does NodeEntity.MetaData inherit from System.Data.Linq.DataContext and is MobileApplication/VideoMobile based on System.Data.Linq.Table<> ? Or are they something else?


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Monday, April 19, 2010 8:13 AM
    Answerer

  • Sorry, yes, I forgot the select in the inner query in my sample. That aside, your corrected version above should work if you use Linq-to-SQL.

    Are you using Linq-to-SQL or Linq-to-something-else? (L2E/EF maybe?)

    ...or rephrased, does NodeEntity.MetaData inherit from System.Data.Linq.DataContext and is MobileApplication/VideoMobile based on System.Data.Linq.Table<> ? Or are they something else?


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

    You are correct. I'm not using Linq-to-SQL, but Linq-to-LLBLGen, but the syntax should be the same. NodeEntity.MetaData is a static property which points to Project.DB.Linq.LinqMetaData. Should I ask this question on the LLBLGen forum?

    Tuesday, April 20, 2010 6:22 AM
  •  

    You are correct. I'm not using Linq-to-SQL, but Linq-to-LLBLGen, but the syntax should be the same. NodeEntity.MetaData is a static property which points to Project.DB.Linq.LinqMetaData. Should I ask this question on the LLBLGen forum?


    Yes and no, although the syntax should be the same it depends on if the underlying provider has implemented support for that and how the underlying provider translates to SQL.

    For LLBLGen you may want to try something like:

    from ma in MetaData.MobileApplication
    join vm in MetaData.VideoMobile on new { ma.ID, videoID } equals { vm.MobileApplicationId, vm.VideoId } into v
    from vm in v.DefaultIfEmpty()
    select new { ma, vm }

     

     


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Tuesday, April 20, 2010 9:14 AM
    Answerer
  • Thanks Kristofer. I've finally got it working! Here is the code:

    Sql syntax: 

    SELECT * FROM mobileApplicationLEFT JOIN videoMobile ON mobileApplication.id = videoMobile.mobileApplicationId	AND videoMobile.videoId = @videoId

     Linq to LLBLGen syntax: 

     var q =
      (
       from ma in NodeEntity.MetaData.MobileApplication
       join vm in NodeEntity.MetaData.VideoMobile on new { mobileApplicationId = ma.Id, videoId = videoId } equals new { mobileApplicationId = vm.MobileApplicationId, videoId = vm.VideoId } into v
       from vm in v.DefaultIfEmpty()
       orderby ma.Name
       select new VideoMobileDetails 
       {
        MobileApplicationName = ma.Name,
        MobileApplicationId = ma.Id,
        FullVideoHigh = vm.FullVideoHigh,
        FullVideoLow = vm.FullVideoLow
       }
      );
    
     //Return the founded results.
     return q.ToList();

     

    Friday, April 23, 2010 9:03 AM