Hi,

I have two tables, both with a projectID. I want to place an inner join on these tables but also inner join all fields with a NULL value ...

So in SQL I can do this like:

INNER JOIN
                      dbo.Subscription ON ISNULL(dbo.AuditInfo.ProjectID, 0) = ISNULL(dbo.Subscription.ProjectID, 0)

Now I am trying to to this in LLBLGen:

IEntityField2 subscriptionProjectIdField = SubscriptionFields.ProjectId;
            subscriptionProjectIdField.ExpressionToApply = new DbFunctionCall("ISNULL", new object[] { SubscriptionFields.ProjectId, 0 });
            IEntityField2 auditInfoProjectIdField = AuditInfoFields.ProjectId;
            auditInfoProjectIdField.ExpressionToApply = new DbFunctionCall("ISNULL", new object[] { AuditInfoFields.ProjectId, 0 });

            IEntityRelation relation = new EntityRelation(auditInfoProjectIdField, subscriptionProjectIdField, RelationType.ManyToMany);
            filter.Relations.Add(relation, JoinHint.Inner);

But the ISNULL functions are not in the generated SQL, there it's just:

INNER JOIN [MyDb].[dbo].[Subscription]  ON  [MyDb].[dbo].[AuditInfo].[ProjectID]=[MyDb].[dbo].[Subscription].[ProjectID])

Isn't it possible to do this with LLBLGen or have I made a mistake somwhere?!?

For the EntityRelation you want to add, set the CustomFilter property to the needed Filter, and the set CustomFilterReplacesOnClause property to true.

The code should look like the following:

IEntityRelation relation = SubscriptionEntity.Relations.AuditInfoEntity....;
relation.CustomFilter = ...;
relation.CustomFilterReplacesOnClause = true;
filter.Relations.Add(relation);

Hi Walaa,

I am trying to do it as you say, but I am stuck here:

IEntityRelation relation = new EntityRelation(AuditInfoFields.ProjectId, SubscriptionFields.ProjectId, RelationType.ManyToMany);

            IEntityField2 subscriptionProjectID = new EntityField2("subscriptionProjectID", new DbFunctionCall("ISNULL", new object[] { SubscriptionFields.ProjectId, 0 }));
            IEntityField2 auditInfoProjectID = new EntityField2("auditInfoProjectID", new DbFunctionCall("ISNULL", new object[] { AuditInfoFields.ProjectId, 0 }));

            IPredicateExpression customFilter = new PredicateExpression();
            customFilter.Add(new FieldCompareSetPredicate(subscriptionProjectIdField, auditInfoProjectIdField, ComparisonOperator.Equal, null));

            relation.CustomFilter = customFilter;
            relation.CustomFilterReplacesOnClause = true;
            filter.Relations.Add(relation, JoinHint.Inner);

it faults on fieldcomparesetpredicate ... what am I doing wrong here?

Best regards,

G.I.

Ok, I fixed it, this is my code:

IEntityRelation relation = new EntityRelation(AuditInfoFields.ProjectId, SubscriptionFields.ProjectId, RelationType.ManyToMany);

            IPredicate customFilter = new EntityField2("auditInfoProjectID", new DbFunctionCall("ISNULL", new object[] { AuditInfoFields.ProjectId, 0 })) 
                == new EntityField2("subscriptionProjectID", new DbFunctionCall("ISNULL", new object[] { SubscriptionFields.ProjectId, 0 }));

            relation.CustomFilter = new PredicateExpression(customFilter);
            relation.CustomFilterReplacesOnClause = true;

            filter.Relations.Add(relation, JoinHint.Inner);