Using DateTime in where clause

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Add a new column to your example excel file, called birthday.  Populate
with valid data.
2. Update User.vb to include Birthday property as a Date type
3. Update Module1.vb to include a where clause 'Where x.Birthday.Year = 1978'

What is the expected output? What do you see instead?
I would expect that record with that birthday year would be selected from
the Excel spreadsheet.  Instead, I get an OleDbException: "No value gieb
for one or more required parameters".  This is the query being issued under
the covers: "SELECT * FROM [Sheet1$] WHERE ([Year] = ?)".  It treats the
year as the name of the column, instead of using birthday.  Is there a
workaround for this at all?

What version of the product are you using? On what operating system?
I am using v 1.0.57 in Visual Studio 2008 web application, on Window Server
2003, SP2 

Please provide any additional information below.

Original issue reported on code.google.com by nosnibor...@gmail.com on 1 Mar 2010 at 10:47

[GoogleCodeExporter]

FYI, I have developed my own solution for a client that has numerous DateTime 
fields
in a couple Excel spreadsheets.  Rather than bog you down with the details of 
that
work, I decided to see if the same issue could be found in your example.  
Because it
is, I decided to use that to get an answer.  Thanks.

Original comment by nosnibor...@gmail.com on 1 Mar 2010 at 10:54

[GoogleCodeExporter]

If you need the fix in a hurry you can use the following statement.

var allUsers = (from x in excel.Worksheet(Of User)()
                select x).ToList();
var seventyEightUsers = from x in allUsers
                        Where x.Birthday.Year = 1978
                        select x;

This fix first selects all the users from the spreadsheet and adds them to an 
in-
memory list. Then it queries the in-memory list for users born in the year 
1978. 

Original comment by paulyo...@gmail.com on 1 Mar 2010 at 11:00

[GoogleCodeExporter]

That works like a charm!  Thanks!  This now give me the ability to order the 
results
instead of having to do that after I select from the speadsheet.  The only 
drawback I
suppose is having these large lists in memory (some are 5K rows).  However, this
application is only a POC used by a handful of users, so I think I can get away 
with
it for now.  

Just curious, is this issue a bug?  Do you plan to fix it?  I realize you have
limited time and resources so it may fall on the back burner.  Thanks for a 
great
piece of code!  It saved me from having to write some pretty nasty parsing code.

Original comment by nosnibor...@gmail.com on 1 Mar 2010 at 11:33

[GoogleCodeExporter]

The issue is caused by using the "Year" property on the BirthDay DateTime 
object. 
LinqToExcel isn't designed to use sub properties of objects (e.g. 
Birthday.Year) and 
it is too big of an enhancement to add.

Now with that being said, I have figured out a better solution that will meet 
your 
needs and only bring back users with birthdays in the year 1978.

var seventyEithUsers = from x in excel.Worksheet(Of User)()
                       where x.Birthday >= new DateTime(1978, 1, 1) &&
                             x.Birthday <= new DateTime(1978, 12, 31)
                       select x;

This solution will keep you from having an in memory list of 5K rows. 

Let me know if this solution works for you.

Original comment by paulyo...@gmail.com on 2 Mar 2010 at 1:24

[GoogleCodeExporter]

Thanks Paul, your reworked solution works great.  This way I can limit the 
number of
records on the query rather than having to bring everything into memory.  Yeah, 
I too
think that is a big enhancement to add, especially when there is a 
workaround... one
just has to think differently about querying dates ranges.  Thank you for all 
your help.

Original comment by nosnibor...@gmail.com on 2 Mar 2010 at 2:26

[GoogleCodeExporter]

Original comment by paulyo...@gmail.com on 2 Mar 2010 at 3:28

• Changed state: Done