How to combine two LINQ queries, but the second query requires more fields
I need to create a connection between two LINQ queries, but for the second query I need to have more fields to come first. How can i do this?
Example:
public static void Dummy()
{
var query1 = this.Db.Table1.Select(s => new MyObject() { A = s.Field1, B = s.Field2 });
var query2 = this.Db.Table2.Select(s => new MyObject() { A = s.Field1, B = s.Field2, C = s.Field3 });
var result = query1.Union(query2);
}
When I call result.ToList () the following error occurs:
The MyObject type appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in a single request, but only if the same properties are set in both places, and those properties are set in the same order.
How can I solve this problem?
Generalization: I cannot put Field3
in query1
(I have no access to the request because I cannot change it)
source to share
You don't need to put Field3
in the first query, but Union
requires the same number of columns and in the same order. Provide a dummy value for the third column / field C
, for example:
var query1 = this.Db.Table1.Select(s => new MyObject()
{ A = s.Field1, B = s.Field2 , C= ""});
Assign C
what is the default Field3
can be null
for reference type and 0
for numbers, etc.
If you don't have access to it, change query1
then create a new request using query1
for example:
var newQuery = query1.Select(s=> new MyObject()
{ A = A, B = B , C= ""});
and then use this in Union
var result = newQuery.Union(query2);
source to share
As-is, you can't. You can only combine 2 sets that have the same structure. If you don't mind changing query1
, however:
var query1 = this.Db.Table1.Select(s => new MyObject()
{ A = s.Field1, B = s.Field2, C = null });
This would allow them to connect correctly as they have the same structure.
source to share
You can do this for example:
Create an object created from MyObject
class MyObjectUnion : MyObject{
}
So the method looks like this:
public static void Dummy()
{
var query1 = this.Db.Table1.Select(s => new MyObject() { A = s.Field1, B = s.Field2 });
var query1modified = this.Db.Table2.Select(s => new MyObjectUnion() { A = s.Field1, B = s.Field2, C = null });
var query2 = this.Db.Table2.Select(s => new MyObjectUnion() { A = s.Field1, B = s.Field2, C = s.Field3 });
var result = query1modified.Union(query2);
}
Works
source to share
Since records in query1 will never have a "C" property, and all records in query2 will have a "C" property, it is unlikely that a record in query1 will be equivalent to a record in query2. The only reason for using Union over Concat is to remove duplicates, and since you cannot use them, you will most likely use Concat instead of Union.
public static void Dummy()
{
var query1 = this.Db.Table1.Select(s => new MyObject() { A = s.Field1, B = s.Field2 });
var query2 = this.Db.Table2.Select(s => new MyObject() { A = s.Field1, B = s.Field2, C = s.Field3 });
var result = query1.ToList().Concat(query2);
}
There are exceptions, as if you had a custom IEqualityComparer for MyObject that ignores the "C" property, or the default value for the "C" property might exist in the record for table2 and you wanted to remove the duplicate, or if there might be duplicates in the query1 or query2 and you wanted them removed, you can still use Concat, but you need to use Distinct before Concat.
Edited to force query1 before concatenation via .ToList ()
Double checkbox with LinqPad, and the following executable had no problem using a data source where the category and city tables were completely different:
void Main()
{
var query1 = Categories.Select(s => new MyObject { A = s.id, B = s.name });
var query2 = Cities.Select(s => new MyObject { A = s.id, B = s.city_name, C = s.location });
var result = query1.ToList().Concat(query2);
result.Dump();
}
public class MyObject
{
public int A {get;set;}
public string B {get;set;}
public object C {get;set;}
}
source to share