Ajit
It's not falling over on the Sum - by trimming the query down to its basics I
can identify that the problem is in the fragment
group new { a.Cn_ref, a.Cn_desc, b.Ct_quan}
but I now have the workaround which I posted
group new { a.Cn_ref, a.Cn_desc, quan = (b != null ) ? b.Ct_quan : 0 }
Paul
-----Original Message-----
From: ProfoxTech [mailto:[email protected]] On Behalf Of Ajit Abraham
Sent: 07 January 2016 18:59
To: [email protected]
Subject: Re: [NF] SQL SELECT to LINQ
Hello Paul,
Maybe this means that for some master records, there are no child records.
And your model does not accept null int.
Try this one on the Sum
.Sum(p=>(int?)p.Ct_quan) ?? 0}
Regards
Ajit Abraham
On 07/01/2016 18:45, Paul Newton wrote:
> Hi Ajit
>
> The following works
>
> var quantities = (from a in Cnames
> join b in Ctrans on a.Cn_ref equals b.Ct_ref into g select new {
> cn_ref = a.Cn_ref, cn_desc = a.Cn_desc, quantity =
> g.ToList().Sum(p=>p.Ct_quan) }) ;
>
> quantities.Dump();
>
> but it fails with an InvalidOperationException (The null value cannot
> be assigned to a member with type System.Int32 which is a non-nullable
> value type) if there is a row in Cnames without any corresponding rows
> in Ctrans
>
> Any ideas? Thanks
>
> Paul
> -----Original Message-----
> From: ProfoxTech [mailto:[email protected]] On Behalf Of
> Ajit Abraham
> Sent: 07 January 2016 14:28
> To: [email protected]
> Subject: Re: [NF] SQL SELECT to LINQ
>
> Hello Paul,
>
> Can you check if the following too works for you?
>
> from a in cn_ref
> join b in cn_desc on a.cn_ref equals b.ct_ref into g select new {
> cn_ref = a.cn_ref, cn_desc = a.cn_desc, quantity =
> g.ToList().Sum(p=>p.ct_quan) }
>
> I got a cast error which I removed by putting the ?? 0 on the Sum.
>
> If it works, then which you feel is faster?
>
> Regards
>
> Ajit Abaham
>
>
>
>
>
>
> On 07/01/2016 13:26, Paul Newton wrote:
>> Many, many thanks Ajit that worked once I changed on a.cn_ref =
>> b.ct_ref to on a.cn_ref equals b.ct_ref
>>
>> Paul
>>
>> -----Original Message-----
>> From: ProfoxTech [mailto:[email protected]] On Behalf Of
>> Ajit Abraham
>> Sent: 07 January 2016 09:38
>> To: [email protected]
>> Subject: Re: [NF] SQL SELECT to LINQ
>>
>> Hello Paul,
>>
>> One possible solution from stackoverflow bit.ly/1TGzfLp
>>
>>
>> from a in cn_ref
>> join b in cn_desc
>> on a.cn_ref = b.ct_ref
>> group new {a.cn_ref, a.cn_desc, b.ct_quan} by new {a.cn_ref,
>> a.cn_desc } into gc select new { cn_ref = gc.Key.cn_ref, cn_desc =
>> gc.Key.cn_Desc, quantity = gc.ToList().Sum(p=>p.ct_quan) }
>>
>> To sort you can wrap the above in brackets and put a
>> .Orderby(x=>x.cn_ref)
>>
>> I tested it on Linqpad. It works - but the SQL output is verbose compared to
>> your SQL statement.
>>
>> Ajit Abraham
>>
>>
>> On 06/01/2016 18:09, Paul Newton wrote:
>>> Hi all
>>>
>>> I am just starting out with LINQ and am struggling a bit so I
>>> wondered if any kind could help me out with converting the following
>>> to LINQ
>>>
>>> select cn_ref, cn_desc, sum(ct_quan) as Quantity from cname join
>>> ctran on cn_ref=ct_ref group by cn_ref,cn_desc order by cn_ref
>>>
>>> Any offers of help would be much appreciated
>>>
>>> Paul Newton
>>>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
