Few GroupJoin in one query

I'm trying to write ObjectQuery with few consistent GroupJoin, it means that there should be one main table selection + few additional LEFT JOIN. I'm doing it as following, with SelectMany method, because without it I can't access field RoleID :

var routesQuery = entities.Routes.Join(
                    entities.Locales,
                    Routes => Routes.LocaleID,
                    Locales => Locales.LocaleID,
                    (Routes, Locales) => new { Routes = Routes }
                ).GroupJoin(
                    entities.LinkRolesPermissions,
                    Routes => Routes.Routes.RouteID,
                    LinkRolesPermissions => LinkRolesPermissions.EntityID,
                    (Routes, LinkRolesPermissions) => new 
                    { 
                        LinkRolesPermissions = LinkRolesPermissions,
                        RoleID = LinkRolesPermissions.SelectMany(
                            LRS => LRS.RoleID,
                            (LRS, RoleID) => new { RoleID = LRS.RoleID }
                        )
                    }
                )
                .SelectMany(
                    LinkRolesPermissions => LinkRolesPermissions.RoleID, 
                    (LinkRolesPermissions, RoleID) => new { RoleID = RoleID }
                 ).GroupJoin(
                    entities.aspnet_Roles, 
                    LRS => LRS.RoleID, 
                    RLS => RLS.RoleId, 
                    (LRS, RLS) => new { LRS = LRS }
                );

All is working, BUT it is working as INNER JOIN 开发者_Python百科in some way, i realized that SelectMany method causes this behavior. It generates this query :

SELECT  1 AS [C1],  1 AS [C2],  [Extent3].[RoleID] AS [RoleID] FROM   [dbo].[Routes] AS [Extent1] 
INNER JOIN [dbo].[Locales] AS [Extent2] ON ([Extent1].[LocaleID] = [Extent2].[LocaleID]) OR (([Extent1].[LocaleID] IS NULL) AND ([Extent2].[LocaleID] IS NULL)) 
INNER JOIN [dbo].[LinkRolesPermissions] AS [Extent3] ON ([Extent1].[RouteID] = [Extent3].[EntityID]) OR (([Extent1].[RouteID] IS NULL) AND ([Extent3].[EntityID] IS NULL))

I removed it and got the following error :

var routesQuery = entities.Routes.Join(
                    entities.Locales,
                    Routes => Routes.LocaleID,
                    Locales => Locales.LocaleID,
                    (Routes, Locales) => new { Routes = Routes }
                ).GroupJoin(
                    entities.LinkRolesPermissions,
                    Routes => Routes.Routes.RouteID,
                    LinkRolesPermissions => LinkRolesPermissions.EntityID,
                    (Routes, LinkRolesPermissions) => new 
                    { 
                        LinkRolesPermissions = LinkRolesPermissions,
                        RoleID = LinkRolesPermissions.SelectMany(
                            LRS => LRS.RoleID,
                            (LRS, RoleID) => new { RoleID = LRS.RoleID }
                        )
                    }
                )
                .GroupJoin(
                    entities.aspnet_Roles, 
                    LRS => LRS.RoleID, 
                    RLS => RLS.RoleId, 
                    (LRS, RLS) => new { LRS = LRS }
                );

The type arguments for method 'System.Linq.Enumerable.SelectMany(System.Collections.Generic.IEnumerable, System.Func>, System.Func)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Could somebody explain me this behavior, please, and advise how to fix it if possible.

Thanks in advance.

You're trying to eagerly load related objects. Use the Include method to do that.

http://msdn.microsoft.com/en-us/library/bb896272.aspx

IQueryable<Route> query = 
  from route in entities.Routes.Include("LinkRolesPermissions.aspnet_Roles")
  where route.Locales.Any()
  select route;

For this to work, you'll have to set up Navigation Properties.

http://msdn.microsoft.com/en-us/library/bb738520.aspx

PS: you don't have to project into an anonymous type each time:

(Routes, Locales) => new { Routes = Routes } 

Could be

(Routes, Locales) => Routes

Well, it might be useful to continue torture LINQ, though now it seems useless and LINQ may be the right case only for short queries containing one table. It was created for arrays and XML, IMO.

For real SQL queries it would be much better to use direct SQL query initialized within EF entity like this :

ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>
(
"SELECT c.Name,c.City, d.Name FROM ContosoEntities.Customer as c " + 
"LEFT JOIN ContosoEntities.Products as d ON d.Name = c.Name " + 
"WHERE c.Country=@ctry", ent
);
query.Parameters.Add(new ObjectParameter("ctry", "Australia"));

All variants of EF using listed here :

• http://www.simple-talk.com/dotnet/.net-framework/entity-framework-the-cribsheet/
• http://www.scip.be/index.php?Page=ArticlesNET12#Generate