Link to home
Start Free TrialLog in
Avatar of ProgAnal
ProgAnalFlag for United States of America

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.file_time) > Convert.ToDateTime("2011-02-28 08:00:00.000"))
                 && (Convert.ToDateTime(inst.file_time) < Convert.ToDateTime("2011-02-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.
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

Why you need to convert inst.file_time to DateTime?  Is that because inst.file_time is a string?

If that's the case your LINQ should be as follows to match your T-SQL version:

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 > "2011-02-28 08:00:00.000"
                 && inst.file_time < "2011-02-28 17:00:00.000"
                 && (l.account__id == 4001)
                 select l.amount).Sum(s => (Decimal?)s);

Open in new window

Avatar of ProgAnal

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
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

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:

db.Log = Console.Out;

Open in new window

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.
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:

var log = new System.IO.StringWriter();

db.Log = log;

// execute your query

var logContent = log.GetStringBuilder().ToString();

Open in new window


// logContent contains the underline SQL query / parameter values executed
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
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 loaded your data locally and I tried the following code:

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();
        }
    }
}

Open in new window


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

Open in new window


So, it seems to be working fine here.  What's file_time's data type in your database?
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).
Oh, and file_time in the instruments table is DateTime.
Avatar of Alpesh Patel
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.
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.