Throwing error in LINQ [Resolved]

Posted by Santosh4u under LINQ on 3/6/2016 | Points: 10 | Views : 406 | Status : [Member] | Replies : 3
Hi any one please let me know why i am getting below error.

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
Additional information: Unable to create a constant value of type 'Infragistics_Example.clsCompanyByPeriod+PivotedClass'. Only primitive types or enumeration types are supported in this context.


below code i am using

List<Invoice> InvoiceDtls;
List<InvoiceDetailsByRows> InvoiceDetailsByRows;
InvoiceDetailsByRows = (from P2 in M4.tbl_Invoice
join PD in M4.tbl_InvoiceDetails on P2.Invoice_Id equals PD.Invoice_Id
// where PD.TAG_ID == TagId
select new InvoiceDetailsByRows
{
InvoiceId = PD.Invoice_Id.Value,
InvoiceDetailsNo = PD.InvoiceDetailsNo,
Amount = PD.Amount.Value,
InvoiceDetailId = PD.InvoiceDetails_Id,
InvoiceDate = PD.InvoiceDate.Value,
}).ToList();

List<PivotedClass> AfterPIVOT;
AfterPIVOT = InvoiceDetailsByRows
.GroupBy(c => c.InvoiceId)
.Select(g => new PivotedClass
{
InvoiceId = g.Key,
JanY1 = g.Where(c => c.InvoiceDate.Month == 1).Sum(c => c.Amount),
FebY1 = g.Where(c => c.InvoiceDate.Month == 2).Sum(c => c.Amount),
MarY1 = g.Where(c => c.InvoiceDate.Month == 3 ).Sum(c => c.Amount),
AprY1 = g.Where(c => c.InvoiceDate.Month == 4 ).Sum(c => c.Amount),
MayY1 = g.Where(c => c.InvoiceDate.Month == 5).Sum(c => c.Amount),
Summary = g.Sum(c => c.Amount),
}).ToList();

InvoiceDtls = (from P2 in M4.tbl_Invoice // if storing this tbl_Invoice data into a variable then using this variable here it's working but while using enity(tbl_Invoice) is throwing error
select new Invoice
{
InvoiceNo = P2.InvoiceNo,
InvoiceDetailsListRows = (from PD in AfterPIVOT
where PD.InvoiceId == P2.Invoice_Id
select new PivotedClass
{
InvoiceId = PD.InvoiceId,
JanY1 = PD.JanY1,
FebY1 = PD.FebY1,
MarY1 = PD.MarY1,
AprY1 = PD.AprY1,
MayY1 = PD.MayY1,
}).ToList()
}).ToList();
return InvoiceDtls;




Responses

Posted by: Rajnilari2015 on: 3/6/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Santosh4u Sir, the exception is happening at this line

...............................

..............................
InvoiceDetailsListRows = (from PD in AfterPIVOT
where PD.InvoiceId == P2.Invoice_Id
..............................................
............................................


Root Cause:
This is not possible in EF's LINQ (when the type is IQuerable , as in ur case).Because, EF understands that, the tables are residing at two different sources. In ur case the sources are

M4.tbl_Invoice (Type is IQuerable) and AfterPIVOT (Type is IEnumerable) .

It treats as both the sources has their own connection (one being from database and the other from in-memory) and hence they are completely separate model. It's not possible for EF to convert this into a SQL statement.

"if storing this tbl_Invoice data into a variable then using this variable here it's working"
That's because, the source gets converted to IEnumerable which works since both the sources are of type IEnumerable and hence the operation is happening in-memory which on the other hand is the same source of origin for the data.

"but while using enity(tbl_Invoice) is throwing error"
Because the data-source has changed - one is IQuerable (Source: DB) while the other being IEnumerable(Source: In-Memory)

Hope this clarifies the doubt.

--
Thanks & Regards,
RNA Team

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Santosh4u on: 3/7/2016 [Member] Bronze | Points: 25

Up
0
Down
Thanks for clarification..i am clear now but may i know is there any other way to achieve this scenario.
if i will load into a variable then i have to load all the bl_Invoice table data but i don't want this, i want to use where condition in the tbl_Invoice table based on parentId.

InvoiceDtls = (from P2 in M4.tbl_Invoice // i want to use where condition here..
select new Invoice
{
InvoiceNo = P2.InvoiceNo,
InvoiceDetailsListRows = (from PD in AfterPIVOT
where PD.InvoiceId == P2.Invoice_Id
select new PivotedClass
{


Thanks

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 3/7/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
tbl_Invoice.Where(Your Condition)


You can always fire the LINQ Extension Methods on IQuerable too. (:

--
Thanks & Regards,
RNA Team

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response