Hi there,

I'm struggling to find the right way to test whether the right side of a join "is null."

e.g.

from po in linq.PurchaseOrder
join pod in linq.PurchaseOrderDetail on po.PurchaseOrderId equals pod.PurchaseOrderId
join si in linq.SupplierItem on pod.ItemId equals si.ItemId into supplierItems
    from si in supplierItems.DefaultIfEmpty()
select new {
    po.PurchaseOrderId,
    SupplierItem = si,
    SupplierItemId = si?.SupplierItemId
};

In the above case, when no matching SupplierItem row is found, si is a "default" blank entity, so SupplierItem is not null, and SupplierItemId has the value 0. I would like them to be null. What is the best way to achieve this? I could of course check for the value of 0 for SupplierItemId if I can guarantee that 0 is not a valid value, but that doesn't seem right.

I did try use DefaultIfEmpty(null) in the query, but that didn't seem to make any difference.

Thank-you in advance for the advice, and my apologies for the general question that may not actually be LLBLGen specific.

Actually, when I tried to do it the "ugly way," I promptly run into another error.

e.g.,

from po in linq.PurchaseOrder
join pod in linq.PurchaseOrderDetail on po.PurchaseOrderId equals pod.PurchaseOrderId
join si in linq.SupplierItem on pod.ItemId equals si.ItemId into supplierItems
    from si in supplierItems.DefaultIfEmpty()
select new {
    po.PurchaseOrderId,
    SupplierItem = si.ItemId == 0 ? null : si,
    SupplierItemId = si?.SupplierItemId
};

At run-time, an exception is thrown:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: 'The parameter at position 2 is of an unsupported type: Entity'

Anyways, I look forward to learning the right (and hopefully pretty) way of handling this case. Thanks again!

You can have the SupplierItemId as null, by using defining it as a nullable int. (int?). So instead of using an anonymous type, define a class, with int? and pass it to the projection.

from po in linq.PurchaseOrder
join pod in linq.PurchaseOrderDetail on po.PurchaseOrderId equals pod.PurchaseOrderId
join si in linq.SupplierItem on pod.ItemId equals si.ItemId into supplierItems
    from si in supplierItems.DefaultIfEmpty()
select new {
    po.PurchaseOrderId,
    SupplierItem = si,
    SupplierItemGuid = si?.Guid,
    SupplierItemDescription = si?.Description
};

Is what I'm doing above, specifically the

SupplierItem = si

bit, even valid/legal? I'm seeing some more strange behaviour when attempting this type of thing, e.g., the SupplierItem entity has some of the correct attributes but not all:

result.SupplierItem.Guid != result.SupplierItemGuid
result.SupplierItem.Description == result.SupplierItemDescription

First would like to confirm that this kind of thing should work before I dig in deeper.

Thank-you

Boone wrote:

Is what I'm doing above,

What Walaa mentioned was to use a specific class instead of an anonymous type, so instead of...

select new {
    po.PurchaseOrderId,
    SupplierItem = si,
    SupplierItemGuid = si?.Guid,
    SupplierItemDescription = si?.Description
};

... it would be

select new MyResult {
    OrderId = po.PurchaseOrderId,
    SupplierItemGuid = si?.Guid,
    SupplierItemDescription = si?.Description
};

Boone wrote:

bit, even valid/legal? I'm seeing some more strange behaviour when attempting this type of thing, e.g., the SupplierItem entity has some of the correct attributes but not all:

result.SupplierItem.Guid != result.SupplierItemGuid
result.SupplierItem.Description == result.SupplierItemDescription

Why is different result.SupplierItem.Guid vs result.SupplierItemGuid? Which are those values?