Joins in LINQ... What do I return?
I want to write a select query that returns all the data in a table? this is how it would go:
IQueryable<Session> sessions = from session in dataContext.Sessions
where (session.EventID.Equals(eventID))
select session;
Now I want to write a select query that joins two tables. This is how it would go:
var x = from s in dataContext.Sessions
from e in dataContext.Events
select new
{
s.SessionName,
s.SessionDescription,
e.EventName
};
So this is the easy part. we just learnt how to write the query. Now consider this. I have a separate data access layer that does all the basic query generation and works as a data interface. I generate the first query here and then in my engine or the middle tier, I refine my query depending on what the UI layer requires. So in a situation like that I have a function in my Data Access Layer that does a table join... what would this function return. I can't return IQueryable<Session> or whatever is the Table Name because I am making a join and so it is no specific table. I am supposed to be returning an anonymous class. How do I return an Anonymous Class from a function. The whole idea of the anonymous class is that the class name is anonymous. So this is what I do.
public object GetSessionAndEventData()
{
if (string.IsNullOrEmpty(this.connectionString))
{
this.connectionString = ConfigurationManager.ConnectionStrings[MADConstants.CONNECTIONSTRING].ToString();
}
if (this.dataContext == null)
{
this.dataContext = new EntitiesDataContext(this.connectionString);
}
var x = from s in dataContext.Sessions
from e in dataContext.Events
select new
{
s.SessionName,
s.SessionDescription,
e.EventName
};
return x;
}
Yes. I just return an object. While I was thinking about this, I did some reading and discovered something quite interesting in the C# 3.0 specification. "Within the same program, two anonymous object initializers, that specify a same sequence of properties of the same names and types, in the same order, will produce the instance of the same anonymous type". The compiler uses the same generated class if there are two object initializers of the same type. This actually makes it possible to cast it back to the original type.
Then what I did was, I just returned an object type from my data access layer, passed it through the middle tier engine, and all the way to UI. here I just bound my datagrid with the object type and voila...! The Datagrid was actually able to bring out the properties and put them in tabular format without requiring any casting.
Don't you just love it when it just works...!