LINQ union problem

[jkvt]

Hi,

I have two LINQ queries that produce two different results, but the fields, while having different names, are essentially the same. I am trying to union them together (although concat in this case should produce the same results), but I am getting an error. My thought is that I somehow need to declare the type of the resulting fields, but I am not sure how to do that and if it is correct. Could anyone guide me down the right path to figure out what is wrong? Thanks very much. I have the code and the error below.

        var gen= from g in db.tbl_Generator
                  join a in db.tbl_Gen_Allocation on g.Unit_ID equals a.Unit_ID
                  join t in strContractType on 1 equals 1
                  where a.Begin_Date <= dteEnd && a.End_Date >= dteBegin
                  select new { g.Generator_Name, g.ISO_Asset_ID, a.Begin_Date, a.End_Date, a.System_ID };

        //Get all gen in this time frame
        var con = from c in db.tbl_Contracts
                  join a in db.tbl_Contracts_Allocation on c.Contract_ID equals a.Contract_ID
                  join t in strContractType on 1 equals 1
                  where a.Begin_Date <= dteEnd && a.End_Date >= dteBegin
                  select new { c.Name, c.Contract_ID, a.Begin_Date, a.End_Date, a.System_ID };

        //union gens and contracts
        var unionResources = gen.Union(con);

Error 1 'System.Linq.IQueryable<AnonymousType#1>' does not contain a definition for 'Union' and the best extension method overload 'System.Linq.ParallelEnumerable.Union<TSource>(System.Linq.ParallelQuery<TSource>, System.Collections.Generic.IEnumerable<TSource>)' has some invalid arguments

[tinstaafl]

If the two var's are queries you should declare them as such. Otherwise the compiler won't know exactly what you're talking about. Also it's my understanding that queries don't actually hold any data until they're enumerated. You probably should use some sort of structure to hold the data from each, maybe a datatable then add each item returned, in each query, as a row to the same table and voila your queries are concatenated

[Ketsuekiame]

tinstaafl is correct, you need to make sure the compiler understands these are result sets and not queries.

Surround your LINQ in brackets and use .AsEnumerable() (This is still only a query but a different type of one)

Once you convert to IEnumerable, you should be able to use Union.

Personally, I can't see what you're trying to achieve with a union. Shouldn't you be using a join based on System_ID?

[jkvt]

Thanks for the responses.

I tried a few different methods here including adding the .AsEnumerable(). That still resulted in Union not being able to be used because of the var. I changed the var to IEnumerable<DataRow> and that didn't work. I tried this though and it seemed to work:

            IEnumerable<DataRow> gen = (from g in db.tbl_Generator
                        join a in db.tbl_Gen_Allocation on g.Unit_ID equals a.Unit_ID
                        join t in strContractType[0] on 1 equals 1
                        where a.Begin_Date <= dteEnd && a.End_Date >= dteBegin
                        select new { g.Generator_Name, g.ISO_Asset_ID, a.Begin_Date, a.End_Date,
                        a.System_ID }).Cast<DataRow>();

The problem is that when it evaluates I now get
"Unable to create a constant value of type 'System.Char'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

Any ideas on that? Thanks again!

[Ketsuekiame]

Do any of those values come through as a single character or byte? Try casting them to either string or byte depending.

Otherwise, that seems a pretty odd error. What line does it occur on?

[jkvt]

No single chars or bytes. The tables this data comes from are in our SQL Server db and were brought in to the entity data model. I am attaching a picture so you can see where the error is since I cannot really cut and paste from this past of VS. You'll also see a gen.ToList(); below the query in the image. Running that line would raise the same exception, but the exception can also be found as you'll see in the image.

The types in that select statement are:

Field Name/Database/C# type

Generator_Name/varchar/String
ISO_Asset_ID/bigint/Int64
Begin_Date/datetime/DateTime
End_Date/datetime/DateTime
System_ID/bigint/Int64

Also, I do not think I answered your questions about the join. Sorry about that.. the results of each of these two queries are a list of contracts (con) and physical entities (gen) that make up a portfolio. I am just looking to make a single list of all of them along with some data that goes along with each. Joining on System_ID would not mean too much in this case.

Thanks!

[tinstaafl]

Your problem might be here,strContractType[0]. If strContractType is a string by adding the brackets and an index you're accessing a character in that string. If this is truly necessary use the ToString method, strContractType[0].ToString().

[jkvt]

Thanks, tinstaafl. That was part of the problem. The remaining issue was caused by the field names in the 'select new' being named differently between the two queries. Below is code that now works. Notice the 'Name =' and 'Asset_Contract_ID =' in the 'select new' statements. Thanks again for the help on this one, tinstaafl and Ketsuekiame.

        var gen = (from g in db.tbl_Generator
                   join a in db.tbl_Gen_Allocation on g.Unit_ID equals a.Unit_ID
                   where a.Begin_Date <= dteEnd && a.End_Date >= dteBegin
                   select new { Name = g.Generator_Name, Asset_Contract_ID = g.ISO_Asset_ID,
                   a.Begin_Date, a.End_Date, a.System_ID }).AsEnumerable();

        var con = (from c in db.tbl_Contracts
                   join a in db.tbl_Contracts_Allocation on c.Contract_ID equals a.Contract_ID
                   where a.Begin_Date <= dteEnd && a.End_Date >= dteBegin
                   select new { Name = c.Name, Asset_Contract_ID = c.Contract_ID, a.Begin_Date,
                   a.End_Date, a.System_ID }).AsEnumerable();

        var final = gen.Union(con);