Solved

LINQ Where Clause on Date Field

Posted on 2008-10-24
11
4,176 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

728 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