locked
Multi table join using Entity framwork 4.1, should I use lambda or LINQ? And how to? RRS feed

  • Question

  • I'm new to the Entity Framework and I'm trying to convert a multi table join to use the Entity framework. The original SQL looked something like this

    JOIN tab_NewsCommunities S1 ON S1.News_ID = tab_News.NewsID
    inner join tab_communities com on S1.Community_ID = com.Community_ID and com.RecordStatus_ID = 2
    JOIN tab_Communities c ON S1.Community_ID = c.Community_ID
    JOIN tab_Favorites uf ON S1.Community_ID = uf2.Community_ID
        WHERE uf.[User_ID] = @UserId 
    
    The new Entity model looks something like this, I've simplified it some. Can anyone help me convert the sql to LINQ or lambda? I've seen a lot of samples on-line but they are all single table joins. Thanks
    entity model
    Friday, June 24, 2011 9:42 PM

Answers

All replies

  • Hi NullReferenceError;

    In Linq to Entity Framework most of the time you do not need to use Join clause. The reason is that the relationship between the tables already give you a automatic Join on the tables. For example if I have the following query I am accessing three tables by just returning the NewCommunity table, this code will be using Lazy loading and can also be done with Eager loading.

    var newsc = from n in ObjectContext.NewsCommunity
          select n;
    
    // Iterates through the NewsCommunity table      
    foreach( var n in newsc )
    {
      // Use fields from the NewsCommunity tables
      Console.WriteLine("New Community ID = " + n.NewsId);
      // Now for each record in NewsCommunity iterate through the 
      // associated records in the Community table
      foreach( var c in n.Communities )
      {
        // Use fields from the Community tables
        Console.WriteLine( "Community Some Field = " + c.SomeField);
        // Now for each record in the Community table iterate through the
        // associated records in the Favorite table	
        foreach( var f in c.Favorites )
        {
          Console.WriteLine("User ID = " + f.UserId);
        }
      }
    } 
    

    So if you can tell use what you want the output to look like and what programming language you are using we will be able to better help you.

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, June 25, 2011 3:23 AM
  • Hi Fernando,

    I'm using C#, and I'm basically try to convert the original stored procedure to use the entity framework and LINQ.  This is really my first attempt at using the entity framework in a complex way.  The original stored procedure basically searched the set of filtered news items.  The problem that I'm having is that the Navigation Properties don't seem to work in the more complex lambada expressions, so staying with straight LINQ is probably best. How you would recommend writing the above sql in LINQ using the entity model?

    Thank you for the help on this!

     

     

    Monday, June 27, 2011 5:07 PM
  • On 6/27/2011 1:07 PM, NullReferenceError wrote:
    > Hi Fernando,
    >
    > I'm using C#, and I'm basically try to convert the original stored
    > procedure to use the entity framework and LINQ. This is really my first
    > attempt at using the entity framework in a complex way. The original
    > stored procedure basically searched the set of filtered news items. The
    > problem that I'm having is that the Navigation Properties don't seem to
    > work in the more complex lambada expressions, so staying with straight
    > LINQ is probably best. How you would recommend writing the above sql in
    > LINQ using the entity model?
    >
     
    You do know you can use Entity SQL to query the conceptual model.
     
     
    Based on your return results, if the results match an entity on the
    model you can instantiate the entity new (var payroll = new Payroll())
    and populate it from the datareader, load the object into a
    List<Payroll> and return the List<T>. You can also make your own custom
    object and return the custom object in a List<T>.
     
    Monday, June 27, 2011 8:17 PM
  • Any update? Would you mind letting us know how it goes?

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, June 28, 2011 2:58 AM
  • This is what I've been able to come up with so far.  I'm still looking into if the results are correct. I'd like to hear from anyone at Microsoft on how they would tackle this problem.  Thanks,

        var result = _context.News
                    .Where(n => n.NewsCommunities.Any(nc => nc.Communities.Favorites.Any( f => f.User_ID == userGuid)));

    Tuesday, June 28, 2011 4:19 PM
  • join keyword was praticed into lambda. i suggest you can check this link. http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/f73fa01a-c6fb-4eb7-aa97-e84f971f99a5

    Thanks,

    Esters


    Just a newbie for everything.
    Wednesday, June 29, 2011 8:59 AM
  • Any update? Would you mind letting us know how it goes?

    Please feel free to let us know if you need further support.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 8, 2011 4:56 PM