Using Linq in a union?

Question

Sql:

SELECT date,total_usage_T1 as TotalUsageValue,'T1' as UsageType FROM TblSayacOkumalari UNION ALL SELECT date,total_usage_T2 as TotalUsageValue,'T2' as UsageType FROM TblSayacOkumalari 

And I'm trying to convert it to linq

 IEnumerable<TblSayacOkumalari> sayac_okumalari = entity.TblSayacOkumalari .Select(x => new { x.date, x.total_usage_T1 }) .Union(entity.TblSayacOkumalari.Select(x => new { x.date, x.total_usage_T2 })); 

But I do not know how to convert 'T1' as UsageType to linq. It is also incorrect to use my union.

My fields in the table:

 | date | total_usage_T1 | total_usage_T2 | | 2010 | 30 | 40 | | 2011 | 40 | 45 | | 2012 | 35 | 50 | 

I want like this

 | date | TotalUsageValue | UsageType | | 2010 | 30 | T1 | | 2011 | 40 | T1 | | 2012 | 35 | T1 | | 2010 | 40 | T2 | | 2011 | 45 | T2 | | 2012 | 50 | T2 | 

I tried very hard, but could not. Please, help.

Answer 1

EDIT

 Def. from MSDN Enumerable.Concat - Concatenates two sequences. Enumerable.Union - Produces the set union of two sequences by using the default equality comparer. 

My message: Concat () vs Union ()

  IEnumerable<TblSayacOkumalari> sayac_okumalari = entity.TblSayacOkumalari .Select(x => new { date= x.date, TotalUsageValue = x.total_usage_T1, UsageType = "T1" }) .Concat(entity.TblSayacOkumalari .Select(x => new { date= x.date, TotalUsageValue = x.total_usage_T2, UsageType = "T2" } )); 

for the type of use you need to add UsageType = "T2" to your new anonymous type, as I did above this will do the task for you

---

Than you should go to the Concat method, but not to the Union method.

Example

  int[] ints1 = { 1, 2, 3 }; int[] ints2 = { 3, 4, 5 }; IEnumerable<INT> union = ints1.Union(ints2); Console.WriteLine("Union"); foreach (int num in union) { Console.Write("{0} ", num); } Console.WriteLine(); IEnumerable<INT> concat = ints1.Concat(ints2); Console.WriteLine("Concat"); foreach (int num in concat) { Console.Write("{0} ", num); } 

output

Facts about the Union and the Conkat

The conclusion shows that the Concat () method simply combines two enumerated collections into single ones, but does not perform any operation / process. Any item simply returns one enumerated collection with the entire element from two enumerated collections.

The Union () method returns an enumerated collection, eliminating the duplicate. If you return only one element, if the same element exists in both enumerated collections on which the union is performed.

It is important to note

• Thus, we can say that Concat () is faster than Union () because it does not perform any processing.

• But if after combining two collections using Concat (), having one collection with too many repeating elements, and if you want to perform further operation on the created collection, it will take longer than the collection created using the Union () method because Union () eliminates duplication and creates a collection with fewer elements.

Answer 2

Use this:

 var result = entity.TblSayacOkumalari .Select(x => new { Date = x.date, TotalUsage = x.total_usage_T1, UsageType = "T1" }) .Union(entity.TblSayacOkumalari.Select(x => new { Date = x.date, TotalUsage = x.total_usage_T2, UsageType = "T2" })); 

Answer 3

To get the expected property names in an anonymous type, you probably want to do something like:

 new { x.date, TotalUsage = x.total_usage_T1, UsageType="T1" } 

and

 new { x.date, TotalUsage = x.total_usage_T2, UsageType="T2" }