Aggregate (Sum) function in LINQ to SQL in C#

I am new to Linq to SQL and C#; but experienced in SQL Server and Visual Studio.

I have the following query in Transact-SQL . . .

SELECT SUM(ledger.amount)
FROM journal
INNER JOIN ledger ON dbo.journal.id = ledger.journal__id
INNER JOIN account ON dbo.account.id = ledger.account__id
INNER JOIN account_type ON account.account_type__id = account_type.id
LEFT OUTER JOIN instrument ON journal.instrument__id = instrument.id
LEFT OUTER JOIN account_recv_info
ON ledger.account_recv_info__id = account_recv_info.id
WHERE (accrue_date > '2011-02-28 00:00:01' AND accrue_date < '2011-02-28 23:59:59')
AND (ledger.account__id = '4001')

... but not sure if I got it right in Linq to SQL, with the aggregate function (Sum) and two outer joins:

ReportDataContext db = new ReportDataContext();

            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 acctyp in db.account_types on a.account_type__id equals acctyp.id
                join inst in db.instruments on j.instrument__id equals inst.id into inner
                from y in inner.DefaultIfEmpty()
                join ari in db.account_recv_infos on l.account_recv_info__id equals ari.id into outer
                from z in outer.DefaultIfEmpty()
                where (((y.file_time.Date >= DateTime.Parse("2/28/2011 00:00:01 AM"))
                && (y.file_time.Date < DateTime.Parse("2/28/2011 12:59:59 PM")))
                && (l.account__id == 4001))
                group z by z.id into result
                select new
                {
                    Sum = result.Sum(z => z.amount)
                };

The error I'm getting involves the Sum , " 'NewFeeBook.account_recv_info' does not contail a definition for 'amount' and no extension method 'amount' accepting a first argument of type 'NewFeeBook.account_recv_info' could be found (are you missing a using directive or an assembly reference?)

The ledgers.amount is a decimal data type and, due to the two outer joins (.DefaultIfEmpty), there are nulls in the results.  I would appreciate any help I can get.
ProgAnalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
Good evening. I'm trying to recreate your scenario. Can you tell me which table has the "file_time" column?
0
Fernando SotoRetiredCommented:
Hi ProgAnal;

If the column amount comes from one of the tables before the line of code:

join inst in db.instruments on j.instrument__id equals inst.id into inner

I suspect the issue is that you are losing the reference to them because of the into clause in the statement.

Fernando
0
ProgAnalAuthor Commented:
Kaufmed,

Sorry, I recently modified the LINQ to SQL for the business customer, switching the datetime field to instruments.file_time, from the TRANSACT-SQL journals.accrue_date.

FernandoSoto:

File_time IS from the instruments table that I'm joining on.  Hope that helps.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ProgAnalAuthor Commented:
FernandoSoto,

If you have a better way to accommodate the two left outer joins (as opposed to my 'into inner' and 'into outer'), I am open to suggestion.
0
Fernando SotoRetiredCommented:
Hi ProgAnal;

Do you have a small test database that you could upload so that we may see as we look at how this may be done. Is so please follow the following instructions:

Can you zip the complete databade files and upload it. Please use the EE site below it will not give you issues with file types,

To upload a file in zip format to the Experts Exchange Stuff web site at http://www.ee-stuff.com follow these steps:

1.  Zip the database files to be uploaded
2.  The go to http://www.ee-stuff.com
3.  If you are prompted to log in use the same username and password you use on the main site here
4.  At the top of the page click on "Expert Area" tab
5.  Then click on "Upload a new file" link
6.  Follow the instructions on the page
7.  After a successful upload post the link of the file in the question.

Fernando
0
ProgAnalAuthor Commented:
You want me to ZIP the entire SQL Server database?  Pardon my hesitation, but I'll have to check with management on whether or not they'll allow something like that (and I'm not certain how to ZIP an entire SQL Server database), since there is some limited access (personal) information.  I'm more than willing to share database design (e.g., datatypes, relationships, etc.), the VS 2010 .dbml file, or whatever else you need.
0
Fernando SotoRetiredCommented:
No I do not want a active database with real data. As I stated "small test database" normally contains invalid data but that can be developed against.
0
ProgAnalAuthor Commented:
The test database we're currently using is a dated copy of the production database.  It does not contain invalid data.  Of course, I can create one just for this purpose, but that will take time.  And we're already dealing with typical Monday morning issues at work.
0
Fernando SotoRetiredCommented:
Just hoping one was available. It would of helped in providing a solution.
0
ProgAnalAuthor Commented:
Oh, I understand.  Wish I had one.  But I'm fairly certain the city/county government would frown on me sharing name, address, mortgage, property, taxes and other such information from our current test database.  Is there anything specific I can answer in the meantime?  Datatypes from a table or two?  Relationships from the .dbml in Visual Studio?
0
ProgAnalAuthor Commented:
Does any expert on here see anything wrong with the syntax of my Linq-to_SQL query?  Is my use of the aggregate function (Sum) correct, or is it preferable to put the results into a list and sum the decimal numbers (including NULLS) from there?  Just looking for some advice without having to create a small test database just for a solution to this.
0
Fernando SotoRetiredCommented:
Hi ProgAnal;

Can you post an image of the table design and relationships between them as can be seen with the DBML design tool. Maybe some of those joines are not needed in the Linq query.

Fernando
0
ProgAnalAuthor Commented:
Hey, FernandoSoto, I took a fresh look at my Linq-toSQL query andfound all the account_recv_info.id's are NULL, so I don't need the second outer join.  This is what I have it down to now:

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 acctyp in db.account_types on a.account_type__id equals acctyp.id
                join inst in db.instruments on j.instrument__id equals inst.id into results
                from r in results.DefaultIfEmpty()
                where (((r.file_time.Date >= DateTime.Parse("2/28/2011 00:00:01 AM"))
                && (r.file_time.Date < DateTime.Parse("2/28/2011 12:59:59 PM")))
                && (l.account__id == 4001))
                select l.amount).Sum();

             Label1.Text = Convert.ToString(RMA);

But I can still post an image of the .dbml, if you like.
0
ProgAnalAuthor Commented:
Here's the image of the .dbml file. DBML for my project
0
Fernando SotoRetiredCommented:
Sorry ProgAnal, one more thing can you please post the SQL statements to create the tables you posted so I can create a skeleton of the database to do some testing.
0
ProgAnalAuthor Commented:
Here you go . . .
CREATE-TO.txt
0
Fernando SotoRetiredCommented:
Is your query returning anything like you want?
0
ProgAnalAuthor Commented:
The TRANSACT-SQL statement it is based on:

SELECT     SUM(ledger.amount) AS Amount
FROM         journal INNER JOIN
                      ledger ON journal.id = ledger.journal__id INNER JOIN
                      account ON account.id = ledger.account__id INNER JOIN
                      account_type ON account.account_type__id = account_type.id LEFT OUTER JOIN
                      instrument ON journal.instrument__id = instrument.id
WHERE     (ledger.account__id = '4001') AND (instrument.file_time > '2011-02-28 00:00:01') AND (instrument.file_time < '2011-02-28 23:59:59')

is returning the correct sum.  With all the fields between the joined tables visible (before whittling it down to just SUM(Ledger.amount), I'm seeing the correct numbers and default (0.00 for nulls).
0
ProgAnalAuthor Commented:
But I am still not getting it to display on my project page correctly.  Any ideas?  Do you see anything I need to adjust/correct in the LINQ-to_SQL query?
0
Fernando SotoRetiredCommented:
You state, "But I am still not getting it to display on my project page correctly", is anything being displayed, is Linq returning a value?
0
ProgAnalAuthor Commented:
I'm getting errors from another project within the solution that are preventing me from testing it fully.  I've got to resolve those first.
0
Fernando SotoRetiredCommented:
Hi ProgAnal;

Well once you get that fixed tell me what you get with this.

var RMA = ( from j in db.journals
            from l in j.ledgers
            let a = l.account
            let acctyp = a.account_type
            join inst in db.instruments on j.instrument__id equals inst.id into results
            join ari in db.account_recv_infos on l.account_recv_info__id equals ari.id
            from r in results.DefaultIfEmpty( )
            where ( ( ( r.file_time.Date >= DateTime.Parse( "2/28/2011 00:00:01 AM" ) )
                  && ( r.file_time.Date < DateTime.Parse( "2/28/2011 12:59:59 PM" ) ) )
                  && ( l.account__id == 4001 ) )
            select (l.amount != null) ? l.amount : 0 ).Sum( s => s );

Open in new window


Fernando
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProgAnalAuthor Commented:
No more errors before rebuilding, which is good.  But my Linq-to-SQL is erring.  It says 'NewFeeBook.NewFeeBookForm.RMA()': not all code paths return a value.  So, I'm assuming it's not handling my NULL values very well.  Let me try your way.  
0
ProgAnalAuthor Commented:
FernandoSoto,

If you look at how your code posted, am I seeing all of the last line (11:) of code?  I know what comes after my parsing of date and time in my WHERE clause.
0
Fernando SotoRetiredCommented:
The last line shows up OK here.

The last line is :

select (l.amount != null) ? l.amount : 0 ).Sum( s => s );
0
ProgAnalAuthor Commented:
Ok, got that line.  Thanks.  But I don't see the line 6 after "join ari in db.account_recv_infos on l.account_recv_info   "
0
ProgAnalAuthor Commented:
Nevermind.  I got it.
0
ProgAnalAuthor Commented:
I get  the following error:
"The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type."
0
käµfm³d 👽Commented:
Jeez. I leave for a few days and you guys go nutz  = )

'NewFeeBook.NewFeeBookForm.RMA()': not all code paths return a value.  So, I'm assuming it's not handling my NULL values very well.

If it's still relevant, that error indicates you have a function that is not void and at some point in your logic there is a chance of not returning anything. Here is a small example:
int MyFunc(int value)
{
    if (value > 3)
    {
        return 0;
    }
    else
    {
        Console.WriteLine("What a great number!");    // <<<----  Hey! Shouldn't I be returning an integer!
    }
}

Open in new window

0
Fernando SotoRetiredCommented:
Where did this come from, "NewFeeBook.NewFeeBookForm.RMA()", ?
0
ProgAnalAuthor Commented:
Project.Page.Function
The point is it's not handling NULLs.  Since the tables of the database are working with decimals, I want the NULL values to be represented as 0.00.  So, (pardom me for not being that familiar with LINQ-to-SQL and C#) how do I incorporate Kaufmed's myFunc into mine?
0
Fernando SotoRetiredCommented:
Try changing the select clause in the query I post to this:

select ( l != null ) ? ((l.amount != null)? l.amount : 0) : 0 ).Sum( s => s );
0
ProgAnalAuthor Commented:
Still getting the following error:
"The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type."
0
käµfm³d 👽Commented:
how do I incorporate Kaufmed's myFunc into mine?
No no no!!!  That was just an example to demonstrate a condition that would raise that particular error (for future reference). It was not intended to be put in your code  = )

Look through your code for a condition where a function could exit without returning a value.
0
käµfm³d 👽Commented:
P.S.

This would only be functions that did not specify void as the return value.
0
Fernando SotoRetiredCommented:
Lets give this a try. Change your select clause to this.

select ( l != null ) ? l.amount : 0 ).Sum( s => (Decimal?) s );
0
ProgAnalAuthor Commented:
I'm thinking something along the lines of (modification of FernandoSoto's last line) . . .

l.amount == null ? "0.00" : Convert.ToString(l.amount)

... but with the Sum aggregate in it somewhere.
0
Fernando SotoRetiredCommented:
See my last post.
0
ProgAnalAuthor Commented:
Oops, spoke too soon.  Let me try FernandoSoto's suggestion.
0
ProgAnalAuthor Commented:
No errors, but not getting a number (sum) displayed either.  I'm gonna have to do some troubleshooting here.
0
Fernando SotoRetiredCommented:
Just for a test remove the where clause and see if you get a value.
0
ProgAnalAuthor Commented:
I un-did my troubleshooting, removed the entire WHERE clause and got a value displayed as (and where) it should!  
0
ProgAnalAuthor Commented:
With this being the TRANSACT-SQL . . .

SELECT     SUM(ledger.amount) AS Amount
FROM         journal INNER JOIN
                      ledger ON journal.id = ledger.journal__id INNER JOIN
                      account ON account.id = ledger.account__id INNER JOIN
                      account_type ON account.account_type__id = account_type.id LEFT OUTER JOIN
                      instrument ON journal.instrument__id = instrument.id
WHERE     (ledger.account__id = '4001') AND (instrument.file_time > '2011-02-28 00:00:01') AND (instrument.file_time < '2011-02-28 23:59:59')

... what is it about the WHERE clause (in LINQ-to-SQL) that's not working?

var RMA =
                (from j in db.journals
                 join l in db.ledgers on j.id equals l.journal__id
                 let a = l.account
                 let acctyp = a.account_type
                 join inst in db.instruments on j.instrument__id equals inst.id into results
                 join ari in db.account_recv_infos on l.account_recv_info__id equals ari.id
                 from r in results.DefaultIfEmpty()
                 where (((r.file_time.Date >= DateTime.Parse("2/28/2011 00:00:01 AM"))
                 && (r.file_time.Date < DateTime.Parse("2/28/2011 12:59:59 PM")))
                 && (l.account__id == 4001))
                 select (l != null) ? l.amount : 0).Sum(s => (Decimal?)s);  
                 
            Label1.Text = Convert.ToString(RMA);
0
Fernando SotoRetiredCommented:
Not being able to see the data, I would say what is in the Where clause that it is filtering every thing.
0
ProgAnalAuthor Commented:
I will have to play with that to get it right.  By the way, do I have the syntax correct for the WHERE clause in LINQ-to-SQL?
0
Fernando SotoRetiredCommented:
Hi ProgAnal;

Well the syntax is correct but I do not think you are filter the way you think you are. I ran a test and found this, DateTime.Parse("2/28/2011 12:59:59 PM"), to return a value one minute to 1:00 PM but this value, DateTime.Parse("2/28/2011 23:59:59 PM"))), to return one minute to midnight. Try the where clause below and see what happens.

where (((r.file_time.Date >= DateTime.Parse("2/28/2011 00:00:01 AM"))
         && (r.file_time.Date < DateTime.Parse("2/28/2011 23:59:59 PM")))
         && (l.account__id == 4001))

Open in new window


Fernando
0
ProgAnalAuthor Commented:
no error, but no value
0
Fernando SotoRetiredCommented:
Well that is the only thing I saw that might of cause it not to return some values.
0
ProgAnalAuthor Commented:
Perhaps (now that you spelled it out above) I am not entering the date (or parsing it) the way I need it to be.  I want all records on 2/28/2011 from a second after midnight (00:00:01 AM) to a second before midnight (12:59:59 PM).  The datetime field, inst.file_time, is in this format (copied from SQL Server 2005 database):

2/28/2011 12:30:00 PM
0
Fernando SotoRetiredCommented:
I would use the the version that uses DateTime.Parse("2/28/2011 23:59:59 PM") seeming it will always be in the range you are looking for.
0
ProgAnalAuthor Commented:
Ok, going with that.
0
ProgAnalAuthor Commented:
... but before I do, here's something I discovered.  I tore apart the TRANSACT-SQL statement, looking at every field, and there were no NULLS in the results that would compel me (why the previous developer whose application I inherited used them, I don't know) to use a LEFT OUTER JOIN.  So, I relaced it with an INNER JOIN (same number of results returned).

The other thing is that the ledger.account__id field throws an error because of the datatype (decimal) after the == .  I had to convert it:

where (Convert.ToString(l.account__id) == "4001")

Now to fix the times . . .

Hey, just because FernandoSoto (and on occasion Kaufmed) and I are going back and forth, if any other Expert on here can make any suggestion to get my datetime to work in the WHERE clause of this LINQ-to-SQL statement, please chime in.
0
ProgAnalAuthor Commented:
Ok, here's what I have it down to:

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 acctyp in db.account_types on a.account_type__id equals acctyp.id
                 join inst in db.instruments on j.instrument__id equals inst.id
                 where (inst.file_time > Convert.ToDateTime("2/28/2011 8:00:00 AM"))
                 && (inst.file_time < Convert.ToDateTime("2/28/2011 5:00:00 PM"))
                 && (l.account__id == 4001)
                 select (l != null) ? l.amount : 0).Sum(s => (Decimal?)s);  

It returns a value of 30.00, but the actual sum of the l.amount's for the records with an l.account__id of 4001 during that timeframe is 1475.  I feel I'm close, but still missing something.  Any help?
0
Fernando SotoRetiredCommented:
What is the value with replacing the where clause wih the following?

where ( l.account__id == 4001 )
0
Fernando SotoRetiredCommented:
Also if you make the same changes, date time values, on the stored procedure what value does it return?
0
ProgAnalAuthor Commented:
No added value.  l.account__id is an integer.  So, it doesn't matter whether I use 'l.account__id == 4001' or 'Convert.ToString(l.account__id) == "4001" ', I get the same results.
0
Fernando SotoRetiredCommented:
So you are telling me that removing the DateTime filter from the WHERE clase and just leaving this l.account__id == 4001, results in no value?
0
ProgAnalAuthor Commented:
And when I change the first date to include transactions (records) dated 2/25/2011 (the 26th and 27th were during the weekend), it returns the value of 805.00!  Transact-SQL for the same where clause returns a sum of 2250.
0
ProgAnalAuthor Commented:
Oh, no, I'm not saying that at all!  When I remove the datetime filter from the where clause, it returns a value of 7360055.50!
0
Fernando SotoRetiredCommented:
Well I am at a lost here. I checked the data type on the database and in Linq mapping and it looks correct. There is one more thing that you can try and that is if you can is to download a product called Linqer. It will take most but not all SQL statements and convert them to Linq. The product you download will be a trial version so I don't know if all functionality is unlocked in the trial. It has been around for a while but I have never tried it. But at this point you have nothing to lose. If you download it make sure you download for for the correct version of the .Net framework.

Linqer
http://www.sqltolinq.com/download

If you do try it out please let me know if it corrected the issue.
0
ProgAnalAuthor Commented:
Of course,
0
ProgAnalAuthor Commented:
FernandoSoto,

Like you, I was stumped.  Kept asking myself, Why would the query work for what amounts to only a few records and seemingly quit?"  Then I got the idea, "What if there's something in the fields that is throwing a monkey wrench into my LINQ-to-SQL statement.  I ran a query against the SQL Server 2005 database and found something.  Within the date range I was testing, there is a record with a -30.00 (negative 30.00) value.  In the design of the database (the CREATE TO scripts I sent a couple days ago), you'll see the amount column of the ledger database table is supposed to be decimal(12, 2).  Now, what to do about handling this peculiar problem?
0
ProgAnalAuthor Commented:
So, I figure your last line ...

select (l != null) ? l.amount : 0).Sum(s => (Decimal?)s);

...must be converted to integer?
0
Fernando SotoRetiredCommented:
This is me NOT having a full understanding at a point along the way when you had the error, "ledger.account__id field throws an error because of the datatype (decimal) ", and in my mind I said maybe he meant amount which is a decimal because account__id  was an int. Well anyway this is what the line of code does. This, (l != null) ? l.amount : 0, which is the select clause is a conditional operator, which takes the part before the "?" and test for true or false, in this case I was testing if the object ledgers existed or not and if it did exit then execute the true part of the conditional operator being  returning l.amount and if it did not exist return the false part which is 0. The lambda expression in the Sum method was do to the fact that the compiler was not happy with this, (s => s);, something about null value so I made it to this this, (s => (Decimal?)s);, converting nulls to Nullable Decimal and it was happy then. So I was making sure something existed before I tried to access its properties.

select (l != null) ? l.amount : 0).Sum(s => (Decimal?)s);
0
ProgAnalAuthor Commented:
I beg your forgiveness.  The only field in the ledger table that is datatype of decimal is amount.  And that's not the field with NULL values (NULL is not allowed).  So, scatch that. So, let's modify your last line to leave out anything having to do with NULLS (there aren't any NULL values, only 0.00).  If we convert the l.amount to integer32 (so it will handle the negative numbers) will your lambda expression "be happy?"
0
ProgAnalAuthor Commented:
or numeric . . .
0
Fernando SotoRetiredCommented:
I had missed your post ID: 35395048 talking about, "you'll see the amount column of the ledger database table is supposed to be decimal(12, 2)", the C# data type Decimal can handle that as well with negative values so no need to convert to integer where you will have data lose due to the convertion.

Now you can try this last line of your query:
select l.amount).Sum( s => s );

But on my system you will have an error, "The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type."

So doing this will get rid of the error but have no data to see if it is going to cause a run time error.
select l.amount).Sum( s => (Decimal?) s );

Have you tried Linqer?
0
ProgAnalAuthor Commented:
select l.amount).Sum( s => s );

...just gives a total of 30.00, not the actual sum of 1425.  No errors.

select l.amount).Sum( s => (Decimal?) s );

...gives the same total of 30.00, but no errors.

I downloaded and tried to install Linqer, but I encountered an error and it quit the installation, even though I clicked Continue.

Can we try the CAST/CONVERT to a more friendly datatype, like int, numeric or even money?
0
Fernando SotoRetiredCommented:
Casting to another data type is not going to bring more values into the calculations. But if you want to try change l.amount in the select clause to the data type you want to try as in the line below which is using integer.

select (int) l.amount).Sum( s => s );
0
ProgAnalAuthor Commented:
Yep, already did.  Got the same results of 30.00.  Again I am at a loss as to why it's just quitting after a couple/three records, and without throwing any error.
0
ProgAnalAuthor Commented:
I totally forgot - GROUP BY!  There are two records in the system with the same id (ledger.id or instrument.id).  The original record entering a fee we thought was collected, and the second with a negative number, effectively cancelling it out when we found no actual fee was collected.
0
ProgAnalAuthor Commented:
Nope, that doesn't make any difference, they cancel each other out no matter how I do it.

Any ideas?
0
Fernando SotoRetiredCommented:
Please try this query as is, make no changes, then tell me what you get.

var RMA = from journals in
            (from journals in db.journals
            join ledgers in db.ledgers on new { id = journals.id } equals new { id = ledgers.journal__id }
            join accounts in db.accounts on new { id = ledgers.account__id } equals new { id = accounts.id }
            join account_types in db.account_types on new { account_type__id = Convert.ToInt32(accounts.account_type__id) } equals new { account_type__id = account_types.id }
            join instruments in db.instruments on new { instrument__id = Convert.ToInt32(journals.instrument__id) } equals new { instrument__id = instruments.id } into instruments_join
            from instruments in instruments_join.DefaultIfEmpty()
            where
              Convert.ToString(ledgers.account__id) == "4001" &&
              Convert.ToString(instruments.file_time) > "2011-02-28 00:00:01" &&
              Convert.ToString(instruments.file_time) < "2011-02-28 23:59:59"
            select new {
              ledgers.amount,
              Dummy = "x"
          })
          group journals by new { journals.Dummy } into g
          select new {
            Amount = (System.Decimal?)g.Sum(p => p.amount)
          }

Open in new window

0
Fernando SotoRetiredCommented:
Hi ProgAnal;

Please disregard my last post I was using your T-SQL code from your post ID: 35385874 where you attempted to change the where clause to strings. Please try this Linq query as is, I believe this will work or be very close.

var RMA = from journals in
              ( from journals in db.journals
                join ledgers in db.ledgers on new { id = journals.id } equals new { id = ledgers.journal__id }
                join accounts in db.accounts on new { id = ledgers.account__id } equals new { id = accounts.id }
                join account_types in db.account_types on new { account_type__id = Convert.ToInt32( accounts.account_type__id ) } equals new { account_type__id = account_types.id }
                join instruments in db.instruments on new { instrument__id = Convert.ToInt32( journals.instrument__id ) } equals new { instrument__id = instruments.id } into instruments_join
                from instruments in instruments_join.DefaultIfEmpty( )
                where
                  ledgers.account__id == 4001 &&
                  instruments.file_time > Convert.ToDateTime( "2011-02-28T00:00:01.000" ) &&
                  instruments.file_time < Convert.ToDateTime( "2011-02-28T23:59:59.000" )
                select new
                {
                    ledgers.amount,
                    Dummy = "x"
                } )
          group journals by new { journals.Dummy } into g
          select new
          {
              Amount = ( System.Decimal? ) g.Sum( p => p.amount )
          };

Open in new window


Fernando
0
käµfm³d 👽Commented:
@FernandoSoto

If you don't get "Expert of the Year" for this one, then I'm calling "shenanigans!"

= D
0
Fernando SotoRetiredCommented:
@kaufmed; Well this one must be the longest one anyone has taken to answer. Maybe a new category is in order.  ;=) 
0
ProgAnalAuthor Commented:
Ok, I put in the code from your latter post.  Where the sum (number) is supposed to be displayed on the web page, I got the following:

SELECT [t6].[value] AS [Amount] FROM ( SELECT SUM([t5].[amount]) AS [value] FROM ( SELECT [t1].[amount], @p0 AS [value], [t1].[account__id], [t4].[file_time] 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].[account_type] AS [t3] ON ([t2].[account_type__id]) = [t3].[id] LEFT OUTER JOIN [dbo].[instrument] AS [t4] ON ([t0].[instrument__id]) = [t4].[id] ) AS [t5] WHERE ([t5].[account__id] = @p1) AND ([t5].[file_time] > @p2) AND ([t5].[file_time] < @p3) GROUP BY [t5].[value] ) AS [t6]
0
Fernando SotoRetiredCommented:
Are you saying that the query is returning a SQL statement back when it is executed?

Try it like this :

var RMA = ( "The query you just used from my last Post goes here" ).SingleOrDefault( );

This will return a single decimal value or a null value.
0
ProgAnalAuthor Commented:
Copied exactly as it is displayed on the web page:

{ Amount = 30.00 }
0
Fernando SotoRetiredCommented:
Will I do not know what else to have you to try, sorry.
0
ProgAnalAuthor Commented:
I would like to try a different day of transaction to test.
0
ProgAnalAuthor Commented:
FernandoSoto,

Are you still with me?  I still need to resolve this and I need your help.  I tried switching to different test sets (different datetime for instrument.file_time) and the inaccurate results continue.  I need a common basis for comparison.  I need the LINQ-to-SQL statement to sum all the records with a datetime during business hours (between 8:00 AM and 5:00 PM).  But the way we currently have the WHERE clause is severely limiting the results.  I've attached an Excel spreadsheet of the records - copied from the results pane when queried via SQL Server 2005 so you can see the instrument_file_time field data exactly how it is in the database.  Looking at this:

instruments.file_time > Convert.ToDateTime( "2011-02-28T00:00:01.000" ) && 
instruments.file_time < Convert.ToDateTime( "2011-02-28T23:59:59.000" )  

What must we do to get the date/time in the same format so it will summ all the records that occur between the two times? test-record-set.xlsx
0
Fernando SotoRetiredCommented:
Hi ProgAnal;

To your question, "What must we do to get the date/time in the same format so it will summ all the records that occur between the two times?", that is what my last attempt on post ID: 35397743 was suppose to due.I think we are at a point where I need you to create a temp database that will reproduce the same results for you so that I can try and see if I can trace down what is going on with this query.

Fernando
0
ProgAnalAuthor Commented:
FernandoSoto,

I really appreciate you working with me on this.  I thought I found what was causing the problem, then not.  So I tore the statement apart, down to its basics.  I did a simple query (no joins or fancy functions) in SQL:

SELECT     id, accrue_date, instrument__id
FROM         journal
WHERE     (accrue_date > '2/28/2011 8:00 AM') AND (accrue_date < '2/28/2011 5:00 PM')

It returns 418 records (see attached file). just-journal-records.xlsx

Then I put it into LINQ-to-SQL,

var RMA =
                 from j in db.journals
                 where j.accrue_date > beginDate
                 && j.accrue_date < endDate
                 select new
                 {
                    journal_id = j.id,
                    journal_accrue_date = j.accrue_date,
                    journal_instrument_id = j.instrument__id,
                 };

display it in a GridView, and all I get is the first 20 records:

1205288      2/28/2011 2:17:21 PM      439696
1205289      2/28/2011 2:17:23 PM      439696
1205290      2/28/2011 2:17:23 PM      439696
1205291      2/28/2011 2:17:23 PM      439696
1205292      2/28/2011 2:17:24 PM      439696
1205293      2/28/2011 2:30:11 PM      439697
1205294      2/28/2011 2:30:11 PM      439697
1205295      2/28/2011 2:30:11 PM      439697
1205296      2/28/2011 2:30:11 PM      439697
1205297      2/28/2011 2:30:11 PM      439697
1205298      2/28/2011 2:31:01 PM      439698
1205299      2/28/2011 2:31:01 PM      439698
1205300      2/28/2011 2:31:01 PM      439698
1205301      2/28/2011 2:31:01 PM      439698
1205302      2/28/2011 2:31:01 PM      439698
1205303      2/28/2011 4:02:59 PM      439699
1205304      2/28/2011 4:02:59 PM      439699
1205305      2/28/2011 4:02:59 PM      439699
1205306      2/28/2011 4:02:59 PM      439699
1205307      2/28/2011 4:02:59 PM      439699

What is going on with this!?!

0
Fernando SotoRetiredCommented:
I don't know what is happening here because I do not know what the values for beginDate and endDate are. Also at a quick glance it looks like the start of the first 20 records by looking at the ID column but look at the time stamp in Linq it is 2:17 PM and in your SQL query 8:13 AM so they can't be the same records.
0
ProgAnalAuthor Commented:
Yeah, I just figured it out.  My LINQ-to-SQL has been going to a static, test database (a dated copy).  But my TRANSACT-SQL has been going to the production database which is dynamic data.
0
Fernando SotoRetiredCommented:
Well that will give different results, so what happens when you point to the SQL Statement to the test database?
0
ProgAnalAuthor Commented:
Let me clean things up, make sure both go to the same database (now), apply your aggregate function, and get back with an answer.
0
ProgAnalAuthor Commented:
The answer is . . . they both give a sum of 30 now (users have been updating records, collecting fees in the l.amount field of the production database).  Here's what I have for beginDate and endDate:

DateTime beginDate = new DateTime(2011, 02, 28, 8, 0, 0);
DateTime endDate = new DateTime(2011, 2, 28, 17, 0, 0);

to rule out anything having to do with the DateTime field(s).  And here is the rest of the routine:

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 into results
                 from r in results.DefaultIfEmpty()
                 where r.file_time > beginDate
                 && r.file_time < endDate
                 && (l.account__id == 4001)
                 select l.amount).Sum();

            Label1.Text = Convert.ToString(RMA);
0
Fernando SotoRetiredCommented:
So it looks like problem solved.
0
ProgAnalAuthor Commented:
I appreciate FenandoSoto sticking with me as I struggled through this.  He gave me a couple of different ways to accomplish it.  My hat's off to, sir! Thank you for your patience and perceverence.
0
Fernando SotoRetiredCommented:
Not a problem ProgAnal, always glad to help a fellow programmer out.

Have a great day.
0
ProgAnalAuthor Commented:
By the way, that's short for Programmer Analyst (my current job title).
0
Fernando SotoRetiredCommented:
Yeah I kind of figured that. Much success.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.