LINQ to Entity RIA Query syntax?

Question

I am using RIA services with Entity Framework- I have the following sql select statement:

select * from TaskTable t, MapTable mt where mt.SiteID=1 and t.EndPointID=mt.EndPointID

How do I write this using method queries and lamda, or any other way I can use in my domain services?

All the examples I see return a new object- do I really have to do this? I thought this would be a pretty simple thing but I haven't found the solultion yet.

I want to do something like:

public void IQueryable<Task> GetTasksFromID(int id)
{
    return this.ObjectContext.TaskTable.Where(e => e.SiteID=id)...????
}

Answer 1

You don't have to put a projection at the end normally - but it looks like you've got two tables involved. Your SQL query is pulling back all the columns, including those from the map table. If you only want the tasks, you don't need those. Try this:

return from task in ObjectContext.TaskTable
       join map in ObjectContext.MapTable
         on task.EndPointID equals map.EndPointID
       where map.SiteID == id
       select task;

(I've been basing the query on the SQL more than the LINQ you provided - from the SQL it looks like SiteID is part of the map rather than the task.)

You can write this in dot notation instead of as a query expression, but it's uglier:

return Object.ContextTable.Join(ObjectContext.MapTable
                                             .Where(map => map.SiteID = id),
                                task => task.EndPointID,
                                map => map.EndPointID,
                                (task, map) => task);

(Note that I've moved the Where clause here as it ends up being simpler before the join itself in this case. You could do the same in the query expression query too, but again it wouldn't look as nice.)

Answer 2

Presuming you have a standard association between Task and Site and that the Site PK is called SiteID, you don't need a join at all. You just do:

public void IQueryable<Task> GetTasksFromID(int id)
{
    return this.ObjectContext.TaskTable.Where(e => e.Site.SiteID=id);
}