8 Replies - 1093 Views - Last Post: 15 April 2015 - 11:44 AM Rate Topic: -----

#1 kyle_denney  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 171
  • Joined: 10-August 12

Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 14 April 2015 - 02:21 PM

Hello,

I am trying to query dates between two textbox values converted to datetime in C#. The dates are returning in the correct datetime and no conversion problem exists there.

However when I run the below LINQ to SQL query in C# it returns no enumerable results. But if I take the below code:

  DbCommand dc = dbHistoryContext.GetCommand(qryRes);
  Console.WriteLine(dc.CommandText);



and get the results as a string of text and cut and paste that into the SQL Server Management studio and execute it - then it works. What do I need to do to search between two dates so that it properly works. The field is of format Date in SQL Server if that matters and the fields being used to pass the information to it are DateTime. The compiler doesn't show any errors but it just doesn't return results.

            var qryRes = (from res in dbHistoryContext.tblR
                         where res.updateddate >= dtDateFrom.Date && res.updateddate <= dtDateTo.Date
                            select res);




Is This A Good Question/Topic? 0
  • +

Replies To: Linq To SQL Query Not Directly Working But SQL Result Text works

#2 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 14 April 2015 - 02:26 PM

Quote

..and get the results as a string of text and cut and paste that into

Copying the string here as well might help.

And what do you do with qryRes (to output the results) after the above code? (Maybe there are rows but you don't iterate them, or output them..?)

This post has been edited by andrewsw: 14 April 2015 - 02:29 PM

Was This Post Helpful? 0
  • +
  • -

#3 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5101
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 14 April 2015 - 02:35 PM

One other thing to double check is that your context is pointing towards the correct database. I chased my tail on that one for hours, then realized I was still pointing to a local development database instead of the server in my configs.

Other than that, how did you enumerate the collection? LINQ is lazily evaluated, so it won't make the call until you actually try to use the results (or call some other method that forces enumeration, like ToList()). If you haven't, try var lst = qryRes.ToList(); and see if lst is empty.
Was This Post Helpful? 0
  • +
  • -

#4 kyle_denney  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 171
  • Joined: 10-August 12

Re: Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 14 April 2015 - 02:41 PM

View Postandrewsw, on 14 April 2015 - 02:26 PM, said:

Quote

..and get the results as a string of text and cut and paste that into

Copying the string here as well might help.

And what do you do with qryRes (to output the results) after the above code? (Maybe there are rows but you don't iterate them, or output them..?)


Here is what it outputs:

SELECT [t0].[resumehistoryid], [t0].[resumeid], [t0].[monsterid], [t0].[resumetitle], [t0].[updateddate], [t0].[updatedstatus], [t0].[source], [t0].[lastmodified], [t0].[modifiedby], [t0].[datechanged], [t0].[monsterhistoryid], [t0].[changetype]
FROM [dbo].[tblResume] AS [t0]
WHERE ([t0].[updateddate] >= @p0) AND ([t0].[updateddate] <= @p1)

where @p0 = '4/01/2015 12:00:00 AM'
and @p1 = '4/30/2015 12:00:00 AM'

and here is how I enumerate the results after the fact of building the query variable:

            foreach (History.tblResume r in qryRes)
            {
                //work with the results here
            }



I double-checked and triple-checked that I am pulling from the right database so I know that is ok. Does anyone see any fallicy in my logic here or anything that would cause the issue?

Thanks!
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 14 April 2015 - 03:12 PM

I would print out the Count of qryRes.

I still suspect that the issue is here:
    //work with the results here

Was This Post Helpful? 0
  • +
  • -

#6 kyle_denney  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 171
  • Joined: 10-August 12

Re: Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 14 April 2015 - 03:17 PM

var lst - the count is zero. So I know it just isn't returning results right? But the generated SQL that I used which I posted works when input the text values in the place of the parameters that LINQ to SQL creates - at least in SQL Server Management Studio.

This post has been edited by andrewsw: 14 April 2015 - 03:22 PM
Reason for edit:: Removed previous quote, just press the REPLY button

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 14 April 2015 - 03:29 PM

I'm back with Curtis's suggestion that you might be pointing at a local database instance, that is recreated, and empty, each time. (See Copy to Output Directory property in this page.) Other than this I'm uninspired at the moment :online2long:



Note that you don't have to quote the previous post, there is a REPLY button further down the page.

This post has been edited by andrewsw: 14 April 2015 - 03:40 PM

Was This Post Helpful? 0
  • +
  • -

#8 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1020
  • View blog
  • Posts: 2,463
  • Joined: 04-October 09

Re: Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 15 April 2015 - 11:23 AM

Try altering the LINQ statement to return all the rows in the table (no where condition). If you get nothing then, I'd say you are using the wrong database, like suggested above.
Was This Post Helpful? 0
  • +
  • -

#9 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5101
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Linq To SQL Query Not Directly Working But SQL Result Text works

Posted 15 April 2015 - 11:44 AM

You can also run a trace on the SQL server to catch your query as it comes in. That can be useful for a few reasons. For one, if you don't catch anything, it means the query is never reaching the DB in the first place (supporting our "pointing at the wrong database" theory). Otherwise, you'll see the SQL that's actually being received by the server, which could help highlight what's going wrong.

You would use the Sql Server Profiler to run a SQL trace: https://msdn.microso...y/ms181091.aspx
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1