Solved

LINQ Where Clause on Date Field

Posted on 2008-10-24
11
4,137 Views
Last Modified: 2013-12-17
I asked this question a couple days ago in the new LINQ section but since I haven't received an answer I'm reposting in a different area.  Anyway, I've been working to convert a fairly complext SQL query to LINQ. I getting stuck on there where clause against the joined table.  See the following attached code snippet that shows the query.  The gotcha is that I can't leave the last line of the where clause in there.

(subae.Field<DateTime?>("EventDate") >= selectedDate || subae.Field<DateTime?>("EventDate") == null)

I get the error "Value cannot be null. Parameter name: row".  If I comment that part out the query runs great and returns (minus what the commented out part would remove) the expected results.  I'm fairly sure it must have something to do with the "DefaultIfEmpty" call and how that probably populates the columns from the other table... but I can't figure out what would work instead.

Thanks for any assistance.

var vw = from a in ds.t1

join ae in ds.t2

on new { f1 = a.Field<long>("ID"), f2 = "DOD" } 

equals new { f1 = ae.Field<long>("ID"), f2 = ae.Field<string>("EventType") } into gj

from subae in gj.DefaultIfEmpty()

where (a.Field<DateTime?>("DOD") == null || a.Field<DateTime?>("DOD") > selectedDate) &&

(subae.Field<DateTime?>("EventDate") >= selectedDate || subae.Field<DateTime?>("EventDate") == null)

select a;

Open in new window

0
Comment
Question by:juliemturner
  • 6
  • 5
11 Comments
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
May I ask, since I am still learning LINQ, what you are trying to achieve with this query?
0
 

Author Comment

by:juliemturner
Comment Utility
I need all the records in table "a" where the field "DOD" is null or where "DOD" is > a selected date and where the left joined table "ae"s field "EventDate" is null or is > the same selected date.  The query I posted here is not actually the real query.  The entire query is SIGNIFICANTLY more complex, but I broke it down into a very simple version that still outlines the problem.  Apparently the syntax for limiting the records in the joined table where that field is a date field doesn't seem to work as one might think it should... as I stated in the original question I think this is becuase of the way the subtable (gj) is generated when using "DefaultIfEmpty".  My guess is that when the where clause is applied to columns in that table where the value is null or whatever value it is causes the error.  Unfortunately I'm too new to LINQ to know what that might be.  I was hoping someone out there would have more experience and have some insight.
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
Well, the fact that I am attempting to answer this question, means that no one did, and I wanted a chance to learn more about LINQ, and hopefully help you at the same time.

I believe that we should start by examining what DefaultIfEmpty really means.  Here is a good example of DefaultIfEmpty.

Enumerable..::.DefaultIfEmpty<(Of <(TSource>)>) Method (IEnumerable<(Of <(TSource>)>), TSource)
http://msdn.microsoft.com/en-us/library/bb355419.aspx

public static void DefaultIfEmptyEx2()
{
    Pet defaultPet = new Pet { Name = "Default Pet", Age = 0 };

    List<Pet> pets1 =
        new List<Pet>{ new Pet { Name="Barley", Age=8 },
                       new Pet { Name="Boots", Age=4 },
                       new Pet { Name="Whiskers", Age=1 } };

    foreach (Pet pet in pets1.DefaultIfEmpty(defaultPet))
    {
        Console.WriteLine("Name: {0}", pet.Name);
    }

    List<Pet> pets2 = new List<Pet>();

    foreach (Pet pet in pets2.DefaultIfEmpty(defaultPet))
    {
        Console.WriteLine("\nName: {0}", pet.Name);
    }
}

You would need to define the default if the result is empty.
0
 

Author Comment

by:juliemturner
Comment Utility
Agreed... however, the example here revolves around a class. I made an attempt at replicating a similar approach for the table with no luck. My failure revolved around the type. In other words in this piece of code what should "ae" be... I tried several things with no luck.
from subae in gj.DefaultIfEmpty
(
new ae {EventID = 0, EventType = "", EventDate = "1/1/1900"}
)
So, when that failed I decided maybe I could force the issue without having to write it in the code by going back to the strongly typed dataset and verifying what the default values are.  I set the date field in the table to "1/1/1900" figuring that I could then just take the null test out of the LINQ query... but I got the same error.  I then decided to just try and select a column from the table created by the join... same error "Value cannot be null. Parameter name: row".  So maybe from a solution perspective it's been simplified.  See the attached code which also doesn't work... and I feel like it should.  Now I'm starting to wonder if the problem lies in the definition of the table in the dataset.

var vw = from a in ds.t1

join ae in ds.t2

on new { f1 = a.Field<long>("ID"), f2 = "DOD" } 

equals new { f1 = ae.Field<long>("ID"), f2 = ae.Field<string>("EventType") } into gj

from subae in gj.DefaultIfEmpty()

where (a.Field<DateTime?>("DOD") == null || a.Field<DateTime?>("DOD")>selectedDate) 

select a.Field<long>("ID"), ae.Field<string>("EventType");

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
Are you working with a DataSet/DataTable, and not a business object class?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:juliemturner
Comment Utility
Yes, sorry if I wasn't clear.  This is a typed dataset.  Two tables (t1) and (t2).
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
I would think that you need to break this problem down into parts, to see what is not working.  I usually like to find simple examples that work, and then add a little more at a time (if it is possible), in order to see what actually starts making it break.  I certainly have tried LINQ with a typed DataSet, so I wouldn't exactly know where the problem could creep in.  Also, I don't have 2008 at work, so it would be difficult to experiment to try to help you find a way.
0
 

Author Comment

by:juliemturner
Comment Utility
Yes, agreed... that's what I'm doing when I got to DefaultIfEmpty not behaving the way I expect.  Thank you for your time and your suggestions.
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
What happens if you leave out DefaultIfEmpty?
0
 

Author Comment

by:juliemturner
Comment Utility
You get an "Enumeration yielded no results" message.  I'm assuming that this is due to the fact that the join yields no records in the resultant table which is expected most of the time.  If it is empty then I want to just ignore the subtable, but if it's not then it may mean that I want to ignore the record from the main table.  You know... thinking aloud, I could just doing a where clause with a subquery to check for that condition... that would potentially solve the problem... it just bugs me that I can't get this working.
0
 

Accepted Solution

by:
juliemturner earned 0 total points
Comment Utility
To close this out, I gave up.  Did the left join as a subquery in the where clause instead.  I am after more signficant testing fairly sure the problem revolved around DefaultIfEmpty it's just not behaving like any examples or references I can find.  So, times up, needed to punt and move on.
Thanks for your help!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now