Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4222
  • Last Modified:

LINQ Where Clause on Date Field

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
juliemturner
Asked:
juliemturner
  • 6
  • 5
1 Solution
 
Bob LearnedCommented:
May I ask, since I am still learning LINQ, what you are trying to achieve with this query?
0
 
juliemturnerAuthor Commented:
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
 
Bob LearnedCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
juliemturnerAuthor Commented:
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
 
Bob LearnedCommented:
Are you working with a DataSet/DataTable, and not a business object class?
0
 
juliemturnerAuthor Commented:
Yes, sorry if I wasn't clear.  This is a typed dataset.  Two tables (t1) and (t2).
0
 
Bob LearnedCommented:
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
 
juliemturnerAuthor Commented:
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
 
Bob LearnedCommented:
What happens if you leave out DefaultIfEmpty?
0
 
juliemturnerAuthor Commented:
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
 
juliemturnerAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now