I'm using 2.6 final, and I'm trying to figure out how to accomplish the following:

SELECT   Ideas.id, Ideas.name, Ideas.description, Ideas.url, Ideas.price, Ideas.createdOn, Ideas.updatedOn, Ideas.properties, Ideas.createdBy, Ideas.updatedBy
FROM         IdeaFacets RIGHT OUTER JOIN
                      Ideas ON IdeaFacets.ideaId = Ideas.id
WHERE    (IdeaFacets.rootId <> '58540F98-04DF-40D9-8AF7-A42663A03FD9') OR
                      (IdeaFacets.rootId IS NULL)

My database is a standard: Idea - IdeaFacet - Facet deal.

Idea: Id

IdeaFacet: IdeaId FacetId RootFacetId

Facet: FacetId

The part that's getting me is saying that the rootid <> some value.

Thanks guys.

Could you post what you have so far, along with the SQL that it generates - this will give us a starting point...

Thanks

Matt

What I've got isn't going to be very helpful, but here you go.

var result = (from idea in MetaData.Idea
                          where idea.IdeaFacets.Count() == 0
                         select idea)
                         .WithPath(parser => parser.Prefetch(entity => entity.IdeaFacets))
                         .ToList();

Query: SELECT COUNT(*) AS [LPAV_] FROM [dbo].[IdeaFacets] [LPLA_2] WHERE ( ( [LPLA_1].[id] = [LPLA_2].[ideaId]))

Generated Sql query: Query: SELECT [LPLA_1].[id] AS [Id], [LPLA_1].[name] AS [Name], [LPLA_1].[description] AS [Description], [LPLA_1].[url]AS [Url], [LPLA_1].[price] AS [Price], [LPLA_1].[createdOn] AS [CreatedOn], [LPLA_1].[updatedOn] AS [UpdatedOn], [LPLA_1].[properties] AS [Properties], [LPLA_1].[createdBy] AS [CreatedBy], [LPLA_1].[updatedBy] AS [UpdatedBy] FROM [dbo].[Ideas] [LPLA_1] WHERE ( ( ( ( ( (SELECT COUNT(*) AS [LPAV_] FROM [dbo].[IdeaFacets] [LPLA_2] WHERE ( ( [LPLA_1].[id] = [LPLA_2].[ideaId]))) = @LPFA_11))))) Parameter: @LPFA_11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.

Generated Sql query: Query: SELECT [dbo].[IdeaFacets].[ideaId] AS [IdeaId], [dbo].[IdeaFacets].[facetId] AS [FacetId], [dbo].[IdeaFacets].[rootId] AS [RootId] FROM [dbo].[IdeaFacets] WHERE ( ( ( [dbo].[IdeaFacets].[ideaId] = @IdeaId1))) Parameter: @IdeaId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: a0b132ca-5a7f-4bc7-bab5-30473bab220f.

The WithPath part confuses me. I don't know if the filter you want is for the main collection or for the prefetch path. Please explain what exactly you want to filter on the main collection and what you want to prefetch.

Anyway, if I remove the WithPath confusion, I think you are looking for something like:

var result = (from idea in MetaData.Idea
                         join ifac in MetaData.IdeaFacets on ifac.IdeaId = idea.Id
                         where ifact.RootId != '58540F98-04DF-40D9-8AF7-A42663A03FD9'
                             || ifact.RootId == NULL                        
                         select idea)
                         .WithPath(parser => parser.Prefetch(entity => entity.IdeaFacets))
                         .ToList();

The "join" in linq allows you to refer to that entity in further predicate filter expressions. This is needed in case you wan to filter on the M side of an 1:M relation (which is your case).

Excellent! That's what I needed. I apologize, I thought I knew LINQ pretty well, and thought that the join syntax was llbl specific. Just realized that it wasn't. Your help let me get to this, as the solution I needed:

var result = (from idea in MetaData.Idea
                         join  ifac in MetaData.IdeaFacet on idea.Id equals ifac.IdeaId into joinstuff
                            from ifac in joinstuff.DefaultIfEmpty()
                         where ifac.RootId != ThingId
                             || ifac.RootId == null                     
                         select idea)
                         .WithPath(parser => parser.Prefetch(entity => entity.IdeaFacets))
                         .ToList();

I apologize for including WithPath initially. It was a red herring. It's there because I need to load the ideafacets with the idea.

Thanks.