Entity Framework returning IQueryable of join of two tables

Question

I'd like to use Entity Framework to return data from 2 tables, and a selection of columns of the 2 tables, at first I was not having much luck with something simple, like returning int's and strings (ie. select new { id = t1.ID, other = t2.OtherData }, as casting that anonymous type was cumbersome at the destination (destination being my winform), so I struck on the idea to just return both table rows...

So something like this :

  public static IQueryable<{Table1,Table2}> byRecID(Guid recID, MyContext DBContext)
  {
      return (from i1 in DBContext.Table1
             join j1 in DBContext.Table2 on i1.GroupID equals j1.GroupID
             where i1.RecID.Equals(RecID)
             select new { i1, j1 }).SingleOrDefault();
  }

This is all fine EXCEPT the return type of the method is incorrect. I've tried a few combinations. Unfortunately, when I call "byRecID" from the winform, "SingleOrDefault" is not available, but IS available inside 'byRecID' method, so I can't just return IQueryable, needs to be the typed IQueryable<SOMETHING IN HERE> (because SingleOrDefault not an extension of IQueryable, only IQueryable<T>).

My Question... is there a syntax for 'SOMETHING IN HERE' that lets me specify its a join of two table rows?

and I'm wondering... Why is SingleOrDefault an option INSIDE the method but not an option of the result of the method when called from my winform?

Basically I was hoping for something that allows clean data calls from my winforms, without casting to hideous anonymous types then using reflection (like when I returned anonymous type of primitives), but also don't want to spawn a Type just for use by my byRecID method.

Answer 1

In C# anonymous types are best used in the scope of the same method where you project them. As you have seen for your self they can't be used outside of the defining method. So you can't return anonymous types from methods (and keep their structure, you can always return them as objects, but this is not good idea) this makes your syntax of the method invalid.

Best option is to project the result to class specified for your needs.

public class TableJoinResult
{
   public Table1 Table1 { get; set; }
   public Table2 Table2 { get; set; }
}

Your query:

 public static IQueryable<TableJoinResult> byRecID(Guid recID, MyContext DBContext)
  {
      return (from i1 in DBContext.Table1
             join j1 in DBContext.Table2 on i1.GroupID equals j1.GroupID
             where i1.RecID.Equals(RecID)
             select new TableJoinResult { Table1= i1, Table2 = j1 }).SingleOrDefault();
  }

More info on anonymous types: https://msdn.microsoft.com/en-us/library/bb397696.aspx