Solved

Concat or Union two or more entities LINQ

Posted on 2010-11-08
2
1,725 Views
Last Modified: 2012-05-10
Hello,

i am trying to do a concat or union between two or more statements, but I am getting this error

Unable to cast object of type 'System.Data.Objects.ObjectQuery`1[VB$AnonymousType_22`2[System.Int32,System.Decimal]]' to type 'System.Collections.Generic.IEnumerable`1[VB$AnonymousType_22`2[System.Int32,System.Nullable`1[System.Decimal]]]'.


In the code below you will see how I have created three seperate linq quiries that provide me with the value based on dt1 and dt2
NOTE: dt1, dt1 are strings that have date values (i.e. start date, end date )


I would like to get the (sum) of  ArAmount between all three tables in one query.


Thank you in advance
Dim qry1 = (From tr In DataContext.TransactionMain _
                              Where tr.TransactionDate >= dt1 _
                              And tr.TransactionDate <= dt2 _
                              And tr.TransactionType = Enums.TransactionType.Completed _
                              Select New With {.arAmount = tr.Amount})


Dim qry2 = (From cm In DataContext.CustomerAccountMemo _
                           Where cm.DateIssued >= dt1 _
                           And cm.DateIssued <= dt2 _
                           Select New With {.arAmount = cm.Amount})

Dim qry3 = (From pm In DataContext.Payment _
                        Where pm.CreatedOn >= dt1 _
                        And pm.CreatedOn <= dt2 _
                        Select New With {.arAmount = pm.Amount * -1})


'Now when I try to merge them as below I get an error Unable to cast object of type 'System.Data.Objects.ObjectQuery`1

Dim qry = (From tr In DataContext.TransactionMain _
                              Where tr.TransactionDate >= dt1 _
                              And tr.TransactionDate <= dt2 _
                              And tr.TransactionType = Enums.TransactionType.Completed _
                              Select New With {.id = tr.TransactionID, .arAmount = tr.Amount}) _
                          .Concat _
                          (From cm In DataContext.CustomerAccountMemo _
                           Where cm.DateIssued >= dt1 _
                           And cm.DateIssued <= dt2 _
                           Select New With {.id = cm.AccountMemoID, .arAmount = cm.Amount}) _
                       .Concat _
                       (From pm In DataContext.Payment _
                        Where pm.CreatedOn >= dt1 _
                        And pm.CreatedOn <= dt2 _
                        Select New With {.id = pm.PaymentID, .arAmount = pm.Amount * -1})


'My ultimate result is that I will have the sum of arAmount between all three tables

Open in new window

0
Comment
Question by:Teknosoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
deadlyDev earned 250 total points
ID: 34091807
There is a mix of types in your query.

At least one of your query (I cannot be sure which without seeing your dbml) fields is a nullable decimal, and the other, or other two are plain decimals.

For whichever fields are not nullable, you should cast the return values to nullable, ie:

Select New With {.id = cm.AccountMemoID, .arAmount = CType(cm.Amount, System.Nullable(Of System.Decimal))}) _
0
 

Author Closing Comment

by:Teknosoft
ID: 34095309
You have identified and directed me to the source of the problem. One entity did have the amount as nullable=false. by just changing that I was able to use the linq query.

Thanks for your help
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Filtering a datagrid view 8 34
visual studio vb.net windows onkeyup 2 30
SQL- GROUP BY 4 25
Import a excel sheet in a grid 2 13
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question