Wednesday, February 24, 2010

Linq to Entity - Workaround Subquery?

For quite sometime, I been trying to figure out how to run a subquery in a Linq to Entity format. I’m kinda old school on querying, I really like to write straight SQL, not linq stuff. And to be honest, I'm not really sure what the right terminology is for some of it....so this post may not be the best in the world.

I’m not really even sure if this is a 'linq subquery' or not, but it is a way of getting nested data. It works for me so that is good.

To give the background on the data being retrieved, I'm getting a User that may have many User_Roster rows tied to it, each User_Roster row has a Book row tied to it, each Book row may have a User_Test row tied to it. So in this example, I need to get down to a certain a User_Test...oh my, hope that makes sense.

//method that uses linq/entity to get data from the database
public static User Get(int userId)
{
   using (FakeEntities eContext = new FakeEntities ())
    {
     User user = eContext.User
     .Include("User_Roster.Book.User_Test")
      .Where(i => .ID.Equals(userId))
      .FirstOrDefault();
    return user;
   }
}

//sample method that would be a consumer of the previous linq query
public string GetUser(string request)
{

//setup some hard coded values for examples sake
  int bookId = 1234;


  //call the method that used linq to query the database
  User item = Get(userId);

  //subquery the list to the exact roster i need
  //on the second line: m is for User_Roster, any variable name works
  User_Roster ur = item.User_Roster
    .Where(m => m.Book.ID.Equals(bookId))
    .FirstOrDefault();

  //if i found the roster i need, get the data i'm looking for
  if (ur != null)
   {
    User_Test test = ur.Book.User_Test .FirstOrDefault();
     if (test != null)
        ....
     }
}

i know there is probably a much smarter way of doing this, but I've not come across any good examples on the internet. so if this post helps you, let me know!! i'm curious.

No comments:

Post a Comment