[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1283
  • Last Modified:

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.
0
ProgAnal
Asked:
ProgAnal
  • 10
  • 7
1 Solution
 
wdosanjosCommented:
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

0
 
ProgAnalAuthor Commented:
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'.

0
 
wdosanjosCommented:
Does the following work?

var start = new DateTime(2011,2,28,8,0,0);
var end = new DateTime(2011,2,28,17,0,0);

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

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ProgAnalAuthor Commented:
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).
0
 
wdosanjosCommented:
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

0
 
ProgAnalAuthor Commented:
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?)
0
 
wdosanjosCommented:
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

0
 
ProgAnalAuthor Commented:
I'm not getting anything.  Is there something else I need to do with it in order to see the output?
0
 
wdosanjosCommented:
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.
0
 
ProgAnalAuthor Commented:
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);).
0
 
wdosanjosCommented:
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
0
 
ProgAnalAuthor Commented:
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
0
 
ProgAnalAuthor Commented:
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.
0
 
wdosanjosCommented:
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?
0
 
ProgAnalAuthor Commented:
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).
0
 
ProgAnalAuthor Commented:
Oh, and file_time in the instruments table is DateTime.
0
 
Alpesh PatelAssistant ConsultantCommented:
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.
0
 
ProgAnalAuthor Commented:
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.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now