Exclude from Index View By Date

BitLost

Active member
Joined
Dec 10, 2016
Messages
35
Programming Experience
Beginner
In my data model I have a listing of jobs. Currently jobs only have two states not invoiced or invoiced. The difference is the invoice date. An open job has null as an invoice date.

In my index view I want to enable an option to view only open jobs. Generally I am not as interested in closed jobs in this view. Whilst all jobs I can view from other views where it is more relevant.

In my SQL table the value as a placeholder inserted by EF is 1/01/0001 12:00 AM. I could alter the data model to allow null here but don't see a point this is good enough for the purpose as I wasn't in business in year 1.

I am struggling to understand how to write the Lambda statement to exclude any entry with a date not equal to this default value.

I have tried:
C#:
[COLOR=blue]public[/COLOR] [COLOR=blue]async[/COLOR] [COLOR=#2b91af]Task[/COLOR]<[COLOR=#2b91af]IActionResult[/COLOR]> Index()
       {
           [COLOR=blue]var[/COLOR] EVAContext = _context.Job
               .Include(j => j.Site)
                   .ThenInclude(j => j.WaterBodys)
                      [COLOR=green]// .ThenInclude(j=>j.WBName)[/COLOR]
               .AsNoTracking()
               .SingleOrDefaultAsync(j=>j.InvoiceDate==[COLOR=blue]null[/COLOR]);


[COLOR=blue]return[/COLOR] View([COLOR=blue]await[/COLOR] EVAContext.ToListAsync());

Which whilst it would be nonsense is pushing an error on ToListAsync.

C#:
[COLOR=blue]var[/COLOR] EVAContext = _context.Job
              .Include(j => j.Site)
                  .ThenInclude(j => j.WaterBodys)
                     [COLOR=green]// .ThenInclude(j=>j.WBName)[/COLOR]
              .AsNoTracking()
              .SingleOrDefaultAsync(j=>j.InvoiceDate!=1/1/0001 etc);
                      
                      
            
 
          [COLOR=blue]return[/COLOR] View([COLOR=blue]await[/COLOR] EVAContext.ToListAsync());
Which would seem to be close to correct...except this gives me an error telling me I cant have not statements on invoice date.I am lost on this. How can I exclude by dates that aren't 1/1/0001 etc?Thanks
 
Why are you calling SingleOrDefaultAsync? What does that method do? Is that what you actually want to do?

As for your filter, the first code snippet should be correct if you have designed your database properly. Using place-holder dates is just bad practice. If there is not date then there should be no date.

If you're going to insist on bad database design then you need to actually provide a DateTime value for that comparison. There's no such thing as a DateTime literal in C# so you need to construct an appropriate DateTime value.
 
Why are you calling SingleOrDefaultAsync? What does that method do? Is that what you actually want to do?

As for your filter, the first code snippet should be correct if you have designed your database properly. Using place-holder dates is just bad practice. If there is not date then there should be no date.

If you're going to insist on bad database design then you need to actually provide a DateTime value for that comparison. There's no such thing as a DateTime literal in C# so you need to construct an appropriate DateTime value.

Thanks for the criticism.

I am learning. I don't say I am an expert. I am learning by copying examples from tutorials and adapting to suit my application. Will I always get it right? No. That's what forums are for.

I am not sure what Single or Default Async does. I think from what I have read it is something to do with asynchronous code processing in Core. But I may have misunderstood.

Adding it is better to have null in the model is sort of helpful and sort of isn't.

So I should have Null. That's great for this situation. However I see every problem I encounter as an opportunity to learn. Whilst setting null fixes this it still leaves the original question unanswered.

Imagine in the real world I am wanting to review the current financial years data. I may want to inject a date in with code to return just records from after a certain date or perhaps I may want to exclude records by a specific date range. Any of this may be possible by explaining my simple enquiry.

As I say I appreciate the criticism on the date not being null. I overlooked it in set up. But this shouldn't be a deal breaker as after all working with dates is a common database issue or is there an alternative approach I haven't seen in tutorials or the couple of books I have (but am slowly reading)?
 
Will I always get it right? No. That's what forums are for.

I am not sure what Single or Default Async does. I think from what I have read it is something to do with asynchronous code processing in Core. But I may have misunderstood.
Forums should not be your first option though. VS has a Help menu for a reason. Why have you not used that Help menu, or even the F1 key, to open the documentation and to read what that method - and in fact any type or method that you are using but are not familiar with - does? Documentation is not written specifically for beginners so you won't always understand it but you should always look first and ask questions later.

There are four similar methods - First, FirstOrDefault, Single and SingleOrDefault - and, in certain situations, their Async forms too. Ignoring the Async part, the choice of which of those four methods to use in any given situation is actually very simple. There's no situation where there isn't one best option.

First: get the first item from a list that must contain at least one item.
FirstOrDefault: get the first item from a list or null if there are no items.
Single: get the only item from a list that must contain one and only one item.
SingleOrDefault: get the only item from a list that must not contain more than one item or null if there are no items.

Given that you are calling ToListAsync afterwards, you are obvious expecting that there will possibly be more than one item so no Single method is appropriate. If there may be no items then call FirstOrDefault (or FirstOrDefaultAsync) and otherwise call First (or FirstAsync).
Adding it is better to have null in the model is sort of helpful and sort of isn't.
It's helpful. If a table has a column that can conceptually hold no data then it should be able to actually hold no data, i.e. it should be nullable. If the column is nullable then you simply test for null before using it. In many cases, such data will automatically be displayed blank so there's no need to test. For instance, the ASP.NET MVC model binder will automatically display an empty string in a view for null values and empty strings in a view will automatically map back to nulls in a controller action.
Imagine in the real world I am wanting to review the current financial years data. I may want to inject a date in with code to return just records from after a certain date or perhaps I may want to exclude records by a specific date range. Any of this may be possible by explaining my simple enquiry.
Like I said, there's no such thing as a DateTime literal in C#. You're trying to a literal DateTime value in the LINQ query and you can't. As I said before, you need to construct a DateTime value and use that. You can either declare a DateTime variable and initialise it and then use that variable in your LINQ query or you can use the same expression directly. As you like opportunities to learn, I'll leave it to you to use the Help menu to open the MSDN documentation, find the topic for the DateTime type and read up on how to construct a value. :)
 
Thanks for a better answer.

I do find help files confusing, and usually a forum is not my first point of call I usually search the internet for something along the lines I am looking for. What isn't helpful is not being sure of the direction to look. This is perhaps the most frustrating part of the entire learning process. I have found it frustratingly difficult to get my head around this language. That's okay. I am not a programmer, I am simply a gifted technical user.

If I worked in a more profitable industry I wouldn't have to work so hard in this area. However needs must. A bit like you deciding too build a house yourself I guess.

Thanks again
 
Just another note or passing comment. Some of the issues I am facing aren't so much my own making or understanding. What I have found is the tutorials often assume prior knowledge that is better explained in older version tutorials. This alone makes it difficult, as sometimes concept clearly spelt out for 3.5 are completely missing for 5.0 and so on.
 
Back
Top Bottom