asp.net core getting null date from linq query

Marius Vasile

5y1.8k.NET

I am using a linq query to get my data from table and I am applying a left join but I couldn't find a way to handle empty dates. Example below

CraftWorkViews = await (from a in _context.WOMains.Where(s => s.OrgID == orgid)
join b in _context.WOPlanners.Where(s => s.Craft == craftid) on a.WOMainID equals b.WOMainID into Temp1
from c in Temp1
join d in _context.WOPlannerSCHs on c.WOMainID equals d.WOMainID into Temp2
from e in Temp2
select new CraftWorkView
{
WONumber = c == null ? 0 : c.WONumber,
Subsequent = c == null ? 0 : c.WONumberS,
Activity = c == null ? " " : c.Activity,
StartDate = e == null ? ???? : e.StartDateP,
EndDate = e == null ? ???? : e.DueDateP,
CompletionDate = e == null ? ???? : e.CompDateP
}).ToListAsync();

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Marius Vasile5y ago
I did an optimization of the tables considering the dates are not null and modified the linq query like
var cwv = await (from a in _context.WOMains.Where(s => s.OrgID == orgid)
                             join b in _context.WOPlanners on a.WOMainID equals b.WOMainID into Temp1
                             from c in Temp1

                             select new CraftWorkView
                             {

                                 WOMainID = a == null ? "" : a.WOMainID,
                                 Activity = c == null ? "" : c.Activity,
                                 WONumber = c == null ? 0 : c.WONumber,
                                 CraftAbr = c == null ? "" : c.Craft,
                                 Subsequent = c == null ? 0 : c.WONumberS,
                                 StartDate = c.StartDateP,
                                 EndDate = c.DueDateP,
                                 CompletionDate = c.CompDateP


                             }).ToListAsync();


            CraftWorkViews = cwv.Where(s => s.CraftAbr == craftid).ToList();
I realized, later, that I have duplicates entries in both tables so I had to do something about it

Thank you both, Rijwan and Jignesh for efforts!
+3
Rijwan Ansari5y ago
Hi Marius,

In this case, you can do in this way. You can change null into default date. Please try and let me know your result.
CraftWorkViews = await (from a in _context.WOMains.Where(s => s.OrgID == orgid)
     join b in _context.WOPlanners.Where(s => s.Craft == craftid) on a.WOMainID equals b.WOMainID into Temp1
     from c in Temp1
     join d in _context.WOPlannerSCHs on c.WOMainID equals d.WOMainID into Temp2
     from e in Temp2
            select new CraftWorkView
            {
                   WONumber = c == null ? 0 : c.WONumber,
                   Subsequent = c == null ? 0 : c.WONumberS,
                   Activity = c == null ? " " : c.Activity,
                   StartDate = e == null ? DateTime.MinValue : e.StartDateP,
                   EndDate = e == null ? DateTime.MinValue : e.DueDateP,
                   CompletionDate = e == null ? DateTime.MinValue : e.CompDateP

              }).ToListAsync();
+2
Jignesh Kumar5y ago
Hi Marius,

If your purpose is left join then need to use DefaultIfEmpty(),
CraftWorkViews = await (from a in _context.WOMains.Where(s => s.OrgID == orgid)
join b in _context.WOPlanners.Where(s => s.Craft == craftid) on a.WOMainID equals b.WOMainID into Temp1
from c in Temp1.DefaultIfEmpty()
join d in _context.WOPlannerSCHs.Where(s => s.Craft == craftid) on c.WOMainID equals d.WOMainID into Temp2
from e in Temp2.DefaultIfEmpty()
    select new CraftWorkView
          {

               WOMainID = a.WOMainID,
               WONumber = c.WONumber,
               Subsequent = c.WONumberS,
               Activity = c.Activity,
               StartDate = e.StartDateP,
               EndDate = e.DueDateP,
               CompletionDate = e.CompDateP


         }).Distinct().ToListAsync();
+2
Marius Vasile5y ago
Thank you Rijwan, I tried that, I get error "cannot convert from DateTime? to DateTime". The table is filled with default(DateTime), sometthing like 1/1/0001 12:00:00 which I will handle in View but the rest of the data is not displayed. It has something to do with the query I guess, because I run through three table maybe?
+1
Rijwan Ansari5y ago
Hi Marius,

Can you try like this
CraftWorkViews = await (from a in _context.WOMains.Where(s => s.OrgID == orgid)
     join b in _context.WOPlanners.Where(s => s.Craft == craftid) on a.WOMainID equals b.WOMainID into Temp1
     from c in Temp1
     join d in _context.WOPlannerSCHs on c.WOMainID equals d.WOMainID into Temp2
     from e in Temp2
            select new CraftWorkView
            {
                   WONumber = c == null ? 0 : c.WONumber,
                   Subsequent = c == null ? 0 : c.WONumberS,
                   Activity = c == null ? " " : c.Activity,
                   StartDate = e == null ? (DateTime?)null : e.StartDateP,
                   EndDate = e == null ? (DateTime?)null : e.DueDateP,
                   CompletionDate = e == null ? (DateTime?)null : e.CompDateP

              }).ToListAsync();
Let me know your result of this query.
+2
Marius Vasile5y ago
I am still not getting the corect results. Either duplicate values or no value at all
CraftWorkViews = await (from a in _context.WOMains.Where(s => s.OrgID == orgid)
join b in _context.WOPlanners.Where(s => s.Craft == craftid) on a.WOMainID equals b.WOMainID into Temp1
from c in Temp1
join d in _context.WOPlannerSCHs.Where(s => s.Craft == craftid) on c.WOMainID equals d.WOMainID into Temp2
from e in Temp2
select new CraftWorkView
{

WOMainID = a.WOMainID,
WONumber = c.WONumber,
Subsequent = c.WONumberS,
Activity = c.Activity,
StartDate = e.StartDateP,
EndDate = e.DueDateP,
CompletionDate = e.CompDateP


}).ToListAsync();
bot variables are coorectly defined orgid and craftid
+1
Marius Vasile5y ago
I handled on the View side, I used normal left join and in View I used and if like below
@if (item.CompletionDate == default(DateTime))
        {
            class="col-md-2">
                class="form-control">--work in progress--
}
else
{
class="col-md-2">
class="form-control">@Html.DisplayFor(modelItem => item.CompletionDate)

}

is there a better way?

Marius Vasile5y ago

I already tried method here

https://www.c-sharpcorner.com/article/linq-join-operations/

but is not working