Few GroupJoin in one query

Question

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 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.

score 1 · Answer 1 · answered Oct 27 '10 at 16:35

1

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

answered Oct 27 '10 at 16:35

Amy B

108,202
21
135
185

I thought that I can use Include only name of the field i merge tables by is the same. And in my case fields are different. LRS => LRS.RoleID, RLS => RLS.RoleId. It also could be - LRS => LRS.RoleID, RLS => RLS.EntityID – Anonymous Oct 27 '10 at 21:33
1

Well, you'd create two navigation properties in that case. Each navigation property would represent a different relationship. – Amy B Oct 28 '10 at 14:15

score 0 · Accepted Answer · answered Feb 26 '11 at 12:36

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 :

P.S. "Include" method joins tables in geometric progression (known bug) so it is appropriate only to join one table, if there will be more than one then in real each table will be merged as many times as "Include" will be used within current query. — Anonymous, Feb 26 '11 at 12:39

Few GroupJoin in one query

2 Answers2