LINQ issue with ExecuteQuery in C#

ikoonman

Hi

Can anyone help me with this problem? In SQL Management Studio the query executes fine, and returns 2 rows, but the last line (with "results.ToList()") of the code snippet below returns a list with 0 items.

(Appointment is a DBML-mapped class)

TIA

public List<Appointment> AppointmentList { get; set; }

public void Populate(DateTime startDate, DateTime stopDate)
{
string sql = string.Format(@"SELECT * FROM Appointments WHERE appointmentdate BETWEEN '{0}' AND '{1}' ORDER BY appointmentdate;", startDate.ToString(), stopDate.ToString());

            using (AppointmentsMapperDataContext mapper = new AppointmentsMapperDataContext(base.DbConnStr))
            {
                var results = mapper.ExecuteQuery<Appointment>(sql);

                this.AppointmentList = results.ToList();
            }
}

Hellm0

Have you captured the query before it gets run? I would suggest investigating the values returned by the .ToString() on those date times you use for start and end dates.

amen

Put a SQL profile on and watch the values going to the database

Is appointmentdate a DateTime Column or a varchar ?

Spunj

Most of the time if you are building a string that compares dates instead of passing them as defined parameters you will get problems.

Try formatting the dates in the string as

WHERE appointmentdate BETWEEN '" + Year(thedate) + '-' + Month(thedate) + '-' + Day(thedate) + "' AND .......

mewso

If you pass the string directly into executequery you can pass in the actual dates as linq will convert them to sql parameters.

public List<Appointment> AppointmentList { get; set; }

public void Populate(DateTime startDate, DateTime stopDate)
{
            using (AppointmentsMapperDataContext mapper = new AppointmentsMapperDataContext(base.DbConnStr))
            {
                var results = mapper.ExecuteQuery<Appointment>("SELECT * FROM Appointments WHERE appointmentdate BETWEEN {0} AND {1} ORDER BY appointmentdate;", startDate, stopDate);

                this.AppointmentList = results.ToList();
            }
}

ikoonman

@Hellm0, @amen - If I execute the query in SQL Management Studio the query is fine - it returns results!

@Spunj, @mewso - Thanks - will try it ASAP

amen

@amen - If I execute the query in SQL Management Studio the query is fine - it returns results!

thats not what I asked.

SQL server provides a profile tool that allows you to see the exact SQL sent from an application to a database, extract the sql and then run in a query window.

you can find SQL Profiler in SQL Enterprise Manager under Tools->SQL Profiler.
This is a very handy tool.


Spunji your Year, Month is fine but you would be quicker doing
CONVERT(DATETIME,thedate)
:rolleyes:

ikoonman

@amen - sorry - misread then. didn't need to try anything else as mewso's answer solved my problem.

Spunj

Spunji your Year, Month is fine but you would be quicker doing
CONVERT(DATETIME,thedate)
:rolleyes:

Thanks for the rolleyes there. I was trying to help him figure out if what he was passing was an invalid date, and that is an easy way to see if the date is being garbled somewhere along the way. AFAIK, no matter what regional settings are on the client/server, SQL Server always accepts a date formatted in YYYY-MM-DD.

CONVERT is a Transact-SQL command and he is building a string from within .NET ("rolleyes"). His variables are already in a DateTime format. His problem was how the SQL Server interpreted the string he eventually passed.

mewso gave a better answer for that exact problem, but I have sometimes needed to build strings dynamically with dates and the way I outlined is one of those that just works.

Asked, answered and mocked

amen

opps sorry about the Rolleyes.

I didn;t meant to add that.

very sorry

Spunj

Apology accepted