NeoSketo NeoSketo - 6 months ago 24
MySQL Question

How to create a linq lambda join query that pulls results even though there null results?

I have this join query that pulls all school programs and products that is in a person's shopping cart:

//this pulls all items the user purchased
var poop = Context.Query<Cart>().Where(x => x.UserId == currentUserId && x.Status == "Archived")
.Select(
p => new
{
p.ItemId,
p.TypeId,
p.PurchaseDate
})
//This get the media type name of the cart items
.Join(
Context.Query<MediaType>(),
t => new {t.TypeId},
m => new {TypeId = m.Id},
(t, m) => new
{
t.ItemId,
t.TypeId,
t.PurchaseDate,
m.TypeName
}).OrderBy(d => d.PurchaseDate)
//Now i need specifics of the items like name, sku, etc. StartDate will be null for items that are products, but contains DateTime for items that are programs.
.Join(
Context.Query<ProgramProductView>(),
e => new {e.ItemId, e.TypeId},
prog => new {ItemId = prog.Id, prog.TypeId},
(e, prog) => new
{
e.ItemId,
e.TypeId,
e.PurchaseDate,
e.TypeName,
prog.FullName,
prog.StartDate,
prog.Sku,
prog.Closed
}).OrderBy(d => d.PurchaseDate);


So right there is where it crashes because prog.StartDate is null for products. I get SQL is not available error.

Is there a way to have the join allow null-able fields? I am only using lambda because it's easier to read and clean.

Answer

Well you just need to use Nullable<> property for you anonymous class in your last Join:

.Join(
    Context.Query<ProgramProductView>(),
    e => new {e.ItemId, e.TypeId},
    prog => new {ItemId = prog.Id, prog.TypeId},
    (e, prog) =>
        new
        {
            ...
            (DateTime?)prog.StartDate,
            ...
         }).OrderBy(d => d.PurchaseDate);

Hope it will help.

Comments