Linq join to perform specific query against Dynamics CRM 2015

Question

I'm looking for some help with a specific Linq join query. My Linq knowledge is fairly basic and I've been struggling all day trying to write the correct join code.

I'm actually trying to build a Linq query that will work with Dynamics CRM Online 2015 SDK using a ServiceContext generated by the CrmSvcUtil.exe utility.

Clearly there are limitations of the CRM Linq Provider (ref1, ref2, ref3 etc.). When using the kind of Linq queries I'm familiar with I often get the error below. It seems the answer is to use a more natural Linq join.

Invalid 'where' condition. An entity member is invoking an invalid property or method.

Rather than show you my 100+ failed attempts I thought it'd be better to use a SQL example to demonstrate what I'm trying to achieve. Example scripts below. Essentially I have an entity for which I wamt to return a list of records. This has two N:N relationships with another entity. I want to return all instances of the main entity where it is associated with a given ID in one N:N relationship, and NOT associated with the same ID in the other N:N relationship.

The part I'm most struggling with is performing a Linq query containing both an inner join and a left outer join. Even if you don't have direct experience with the CRM Linq Provider it may still help me just to see how this would be done normally in Linq. All help much appreciated.

SQL query I want to build with Linq:

DECLARE @id INT = 1

-- Should only return entities with IDs 1 and 2
SELECT a.* FROM [dbo].[MainEntity] a
INNER JOIN [dbo].[AltOne] b ON a.EntityID = b.EntityID AND b.AltOneID = @id
LEFT JOIN [dbo].[AltTwo] c ON a.EntityID = c.EntityID AND c.AltOneID = @id
WHERE c.AltOneID IS NULL

Database setup script:

CREATE TABLE [dbo].[MainEntity](
    [EntityID] [int] NOT NULL,
    [EntityName] [varchar](50) NOT NULL,
    CONSTRAINT [PK_MainEntity] PRIMARY KEY CLUSTERED 
    ( 
        [EntityID] ASC 
    )
)
GO

CREATE TABLE [dbo].[AltOne](
    [EntityID] [int] NOT NULL,
    [AltOneID] [int] NOT NULL,
    CONSTRAINT [PK_AltOne] PRIMARY KEY CLUSTERED 
    (
        [EntityID] ASC,
        [AltOneID] ASC
    )
)
GO

ALTER TABLE [dbo].[AltOne]  WITH CHECK ADD  CONSTRAINT [FK_AltOne_MainEntity] FOREIGN KEY([EntityID])
REFERENCES [dbo].[MainEntity] ([EntityID])
GO

ALTER TABLE [dbo].[AltOne] CHECK CONSTRAINT [FK_AltOne_MainEntity]
GO

CREATE TABLE [dbo].[AltTwo](
    [EntityID] [int] NOT NULL,
    [AltOneID] [int] NOT NULL,
    CONSTRAINT [PK_AltTwo] PRIMARY KEY CLUSTERED 
    (
        [EntityID] ASC,
        [AltOneID] ASC
    )
)
GO

ALTER TABLE [dbo].[AltTwo]  WITH CHECK ADD  CONSTRAINT [FK_AltTwo_MainEntity] FOREIGN KEY([EntityID])
REFERENCES [dbo].[MainEntity] ([EntityID])
GO

ALTER TABLE [dbo].[AltTwo] CHECK CONSTRAINT [FK_AltTwo_MainEntity]
GO

INSERT INTO [dbo].[MainEntity] ([EntityID], [EntityName]) VALUES (1, 'Test 1')
INSERT INTO [dbo].[MainEntity] ([EntityID], [EntityName]) VALUES (2, 'Test 2')
INSERT INTO [dbo].[MainEntity] ([EntityID], [EntityName]) VALUES (3, 'Test 3')
GO

INSERT INTO [dbo].[AltOne] ([EntityID], [AltOneID]) VALUES (1, 1)
INSERT INTO [dbo].[AltOne] ([EntityID], [AltOneID]) VALUES (1, 2)
INSERT INTO [dbo].[AltOne] ([EntityID], [AltOneID]) VALUES (2, 1)
INSERT INTO [dbo].[AltOne] ([EntityID], [AltOneID]) VALUES (2, 2)
INSERT INTO [dbo].[AltOne] ([EntityID], [AltOneID]) VALUES (3, 1)
GO

INSERT INTO [dbo].[AltTwo] ([EntityID], [AltOneID]) VALUES (3, 1)
INSERT INTO [dbo].[AltTwo] ([EntityID], [AltOneID]) VALUES (1, 2)
INSERT INTO [dbo].[AltTwo] ([EntityID], [AltOneID]) VALUES (2, 2)
GO

Edit 1:

Adding example classes as requested. To reiterate, I need to return a collection of MainEntity objects without directly using its ICollection properties and using joins instead (this appears to be a limitation of the CRM Linq Provider). The list must be objects that are not related to a particular RelatedEntity via CollectionOne, but are related to the same RelatedEntity via CollectionTwo. I hope this is clear.

public class MainEntity
{
    public int EntityID { get; set; }
    public string EntityName { get; set; }

    public ICollection<RelationshipOne> CollectionOne { get; set; }
    public ICollection<RelationshipTwo> CollectionTwo { get; set; }
}

public class RelationshipOne
{
    public int EntityID { get; set; }
    public int AltOneID { get; set; }

    public ICollection<MainEntity> MainEntities { get; set; }
    public ICollection<RelatedEntity> RelatedEntities { get; set; }
}

public class RelationshipTwo
{
    public int EntityID { get; set; }
    public int AltOneID { get; set; }

    public ICollection<MainEntity> MainEntities { get; set; }
    public ICollection<RelatedEntity> RelatedEntities { get; set; }
}

public class RelatedEntity
{
    public int RelatedEntityID { get; set; }
    public string RelatedEntityName { get; set; }

    public ICollection<RelationshipOne> RelationshipOnes { get; set; }
    public ICollection<RelationshipTwo> RelationshipTwos { get; set; }
}

public class DummyContext
{
    public System.Data.Entity.DbSet<MainEntity> MainEntitySet { get; set; }
    public System.Data.Entity.DbSet<RelationshipOne> RelationshipOneSet { get; set; }
    public System.Data.Entity.DbSet<RelationshipTwo> RelationshipTwoSet { get; set; }
    public System.Data.Entity.DbSet<RelatedEntity> RelatedEntitySet { get; set; }
}

Answer 1

The problem lies in your requirement:

I want to return all instances of the main entity where it is associated with a given ID in one N:N relationship, and NOT associated with the same ID in the other N:N relationship.

You cannot accomplish this with a Linq query in Dynamics CRM. Linq queries for Dynamics CRM are converted into QueryExpression queries. Using QueryExpression it is not possible to select records that are not associated to other records.

Also important to mention: left outer joins are not supported by LINQ for CRM, but are supported by QueryExpression queries.

Your only option is to select (hopefully) a few records more and filter the records not needed afterwards.

Answer 2

A word of warning: the following worked for me when I had to face a similar requirement, but it could be a performance hit to the system depending on data, other customizations, etc. Thorough testing is a must.

You can "cheat" your way through this with a plugin.

1. add a whole number field to MainEntity for each relationship you need to keep track of
2. Build a plugin which counts related records and updates the newly added fields in MainEntity (this should be registered as a Synchronous Post-Operation both on Retrieve/RetrieveMultiple messages). Feed it fields and relationships names through unsecure configuration for reusability purposes.

You can now query MainEntity and know everything you need, no more explicit joins are needed (you can also have the list as a View should you need/want it).