?
Solved

LINQ Where Clause on Date Field

Posted on 2008-10-24
11
Medium Priority
?
4,194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 96

Expert Comment

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

Author Comment

by:juliemturner
ID: 22805418
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
ID: 22807264
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
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 

Author Comment

by:juliemturner
ID: 22812180
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
ID: 22812422
Are you working with a DataSet/DataTable, and not a business object class?
0
 

Author Comment

by:juliemturner
ID: 22812444
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
ID: 22812520
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
ID: 22812534
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
ID: 22812569
What happens if you leave out DefaultIfEmpty?
0
 

Author Comment

by:juliemturner
ID: 22812657
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
ID: 22821724
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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

771 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