ProgAnal
asked on
Datetime data type in LINQ-to-SQL C# statements
I have the following TRANSACT-SQL query:
SELECT SUM(ledger.amount)
FROM journal
INNER JOIN ledger ON ledger.journal__id = journal.id
INNER JOIN account ON account.id = ledger.account__id
INNER JOIN instrument ON instrument.id = journal.instrument__id
WHERE (instrument.file_time > '2011-02-28 08:00:00.000')
AND (instrument.file_time < '2011-02-28 17:00:00.000')
AND ledger.account__id = '4001'
that I need in LINQ-to-SQL (thus far):
var RMA =
(from j in db.journals
join l in db.ledgers on j.id equals l.journal__id
join a in db.accounts on l.account__id equals a.id
join inst in db.instruments on j.instrument__id equals inst.id
where (Convert.ToDateTime(inst.f ile_time) > Convert.ToDateTime("2011-0 2-28 08:00:00.000"))
&& (Convert.ToDateTime(inst.f ile_time) < Convert.ToDateTime("2011-0 2-28 17:00:00.000"))
&& (l.account__id == 4001)
select l.amount).Sum(s => (Decimal?)s);
but the resultant SUM returned is 30.00 instead of the actual value of 1475.00. It's almost as though my datetime from SQL server 2005 (inst.file_time) is filtering out too many records.
ANy help on this would be appreciated.
SELECT SUM(ledger.amount)
FROM journal
INNER JOIN ledger ON ledger.journal__id = journal.id
INNER JOIN account ON account.id = ledger.account__id
INNER JOIN instrument ON instrument.id = journal.instrument__id
WHERE (instrument.file_time > '2011-02-28 08:00:00.000')
AND (instrument.file_time < '2011-02-28 17:00:00.000')
AND ledger.account__id = '4001'
that I need in LINQ-to-SQL (thus far):
var RMA =
(from j in db.journals
join l in db.ledgers on j.id equals l.journal__id
join a in db.accounts on l.account__id equals a.id
join inst in db.instruments on j.instrument__id equals inst.id
where (Convert.ToDateTime(inst.f
&& (Convert.ToDateTime(inst.f
&& (l.account__id == 4001)
select l.amount).Sum(s => (Decimal?)s);
but the resultant SUM returned is 30.00 instead of the actual value of 1475.00. It's almost as though my datetime from SQL server 2005 (inst.file_time) is filtering out too many records.
ANy help on this would be appreciated.
ASKER
No, it's not a string. I originally thought I didn't need to convert anything. The instrument.file_time field (left side) is a datetime data type in SQL Server 2005. The right side ("2011-02-28 08:00:00.000") is modified after taking the field value right out of the database. When I put it just the way you have it (abv) VS2010 intellisense tells me, "Operator '>' cannot be applied to operand of the type 'System.DateTime' and 'string'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works form the aspect of not giving any errors, but the results are the same - 30.00 (not the correct sum of 1475.00). I doubt that it matters, but in the database I inherited, the ledger.amount field is of the decimal datatype. There is a negative number amount the results records (it cancels out a positive 30.00 entry made earlier that day).
BTW, you should be able to view the underline SQL query with the following code:
var query = from j in db.journals
join l in db.ledgers on j.id equals l.journal__id
join a in db.accounts on l.account__id equals a.id
join inst in db.instruments on j.instrument__id equals inst.id
where inst.file_time > start
&& inst.file_time < end
&& (l.account__id == 4001)
select l.amount;
string sql = ((ObjectQuery<Decimal?>)query).ToTraceString();
// sql contains the underline SQL query
ASKER
Not quite. Intellisense indicates, "The typed name or namespace name 'ObjectQuery' could not be found (are you missing a using directive or an assembly reference?)
Sorry, my bad that won't work with LINQ2SQL. That's for LINQ2Entities.
Use the following to output the underline SQL/parameters to stdout:
Use the following to output the underline SQL/parameters to stdout:
db.Log = Console.Out;
ASKER
I'm not getting anything. Is there something else I need to do with it in order to see the output?
No, not really. If you have a WinForm or WPF app, you should see the output on the Output view (Ctrl+W, O or View / Output) then select Show output from: Debug. If it's a console app, it will display on the cmd window.
ASKER
It's a web app. I'm viewing it in the browser to see the results (I display RMA in a label on the web form - Label1.Text = Convert.ToString(RMA);).
OK. Please try the following:
// logContent contains the underline SQL query / parameter values executed
var log = new System.IO.StringWriter();
db.Log = log;
// execute your query
var logContent = log.GetStringBuilder().ToString();
// logContent contains the underline SQL query / parameter values executed
ASKER
I modified my query and databound the results to a GridView to look over the results. Here are the records it's returning (the Sum is of the 6th column). Right away I noticed the instrument.file-time column (last column) is in a different format than the other records which are in 24-hour format.
1205288 439696 6785904 1205288 4001 5.00 4001 Real-Estate Revenue 4 Revenue from recording fee and misc fees 439696 2011 2746 147 2/28/2011 2:17:21 PM
1205293 439697 6785914 1205293 4001 20.00 4001 Real-Estate Revenue 4 Revenue from recording fee and misc fees 439697 2011 2747 147 2/28/2011 2:30:11 PM
1205298 439698 6785924 1205298 4001 5.00 4001 Real-Estate Revenue 4 Revenue from recording fee and misc fees 439698 2011 2748 147 2/28/2011 2:31:01 PM
1205303 439699 6785934 1205303 4001 0.00 4001 Real-Estate Revenue 4 Revenue from recording fee and misc fees 439699 2011 2749 147 2/28/2011 4:02:59 PM
I'm uploading a file of all the records returned from the TRANSACT-SQL query. I selected all and formatted only the decimal (l.amount) into currency and the inst.file-time is as close as Excel would allow (SQL Server provides seconds and beyond). test-record-set.xlsx
1205288 439696 6785904 1205288 4001 5.00 4001 Real-Estate Revenue 4 Revenue from recording fee and misc fees 439696 2011 2746 147 2/28/2011 2:17:21 PM
1205293 439697 6785914 1205293 4001 20.00 4001 Real-Estate Revenue 4 Revenue from recording fee and misc fees 439697 2011 2747 147 2/28/2011 2:30:11 PM
1205298 439698 6785924 1205298 4001 5.00 4001 Real-Estate Revenue 4 Revenue from recording fee and misc fees 439698 2011 2748 147 2/28/2011 2:31:01 PM
1205303 439699 6785934 1205303 4001 0.00 4001 Real-Estate Revenue 4 Revenue from recording fee and misc fees 439699 2011 2749 147 2/28/2011 4:02:59 PM
I'm uploading a file of all the records returned from the TRANSACT-SQL query. I selected all and formatted only the decimal (l.amount) into currency and the inst.file-time is as close as Excel would allow (SQL Server provides seconds and beyond). test-record-set.xlsx
ASKER
Correction: there’s no difference in the time formats . . . that’s just the way it copied/displayed.
I was looking for some sort of trend. All I see so far is that these are the first four records in the test set.
I was looking for some sort of trend. All I see so far is that these are the first four records in the test set.
I loaded your data locally and I tried the following code:
Here is the output:
So, it seems to be working fine here. What's file_time's data type in your database?
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication3
{
class Program
{
static void Main(string[] args)
{
using (var db = new AccountingDataContext())
{
var start = new DateTime(2011, 2, 28, 8, 0, 0);
var end = new DateTime(2011, 2, 28, 17, 0, 0);
db.Log = Console.Out;
var RMA =
(from j in db.journals
join l in db.ledgers on j.id equals l.journal_id
join a in db.accounts on l.account_id equals a.id
join inst in db.instruments on j.instrument_id equals inst.id
where inst.file_time > start
&& inst.file_time < end
&& (l.account_id == 4001)
select l.amount).Sum(s => (Decimal?)s);
Console.WriteLine("RMA = {0}", RMA);
}
Console.In.ReadLine();
}
}
}
Here is the output:
SELECT SUM([t4].[value]) AS [value]
FROM (
SELECT [t1].[amount] AS [value], [t3].[file_time], [t1].[account_id]
FROM [dbo].[journal] AS [t0]
INNER JOIN [dbo].[ledger] AS [t1] ON [t0].[id] = [t1].[journal_id]
INNER JOIN [dbo].[account] AS [t2] ON [t1].[account_id] = [t2].[id]
INNER JOIN [dbo].[instrument] AS [t3] ON [t0].[instrument_id] = [t3].[id]
) AS [t4]
WHERE ([t4].[file_time] > @p0) AND ([t4].[file_time] < @p1) AND ([t4].[account_i
d] = @p2)
-- @p0: Input DateTime (Size = -1; Prec = 0; Scale = 0) [2/28/2011 8:00:00 AM]
-- @p1: Input DateTime (Size = -1; Prec = 0; Scale = 0) [2/28/2011 5:00:00 PM]
-- @p2: Input Int (Size = -1; Prec = 0; Scale = 0) [4001]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
RMA = 1475.0000
So, it seems to be working fine here. What's file_time's data type in your database?
ASKER
So, the actual LINQ-to-SQL statement is correct. Great! Thanks for helping me with this. I've been looking at my project in a different way. I need to correct it and try the statement as we have it now. This may take a bit (and, of course, I'll share what I discovered).
ASKER
Oh, and file_time in the instruments table is DateTime.
Hi First execute Linq Query and see its behind for T-SQL and check is it right or wrong? and make change in Linq appropriate.
ASKER
This would have been solved rather quickly if I checked first to make sure my test database (for the VS2010 & LINQ-to-SQL) was also where I was running the TRANSACT-SQL. I appreciate you hanging in there with me as we worked through it. You've also given me a couple more tools to use as I move forward.
If that's the case your LINQ should be as follows to match your T-SQL version:
Open in new window