Linq union usage?

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 try to do 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 dont know how to convert 'T1' as UsageType to linq. Also my union using is incorrect too.

My table fields like this:

| 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.

Solution 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 post : 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 usage type you juse need to add UsageType = "T2" in your new anonymous type as i did above this will do the task for you

---

Than you should go for Concat method rather than 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

Fact about Union and Concat

The output shows that Concat() method just combine two enumerable collection to single one but doesn't perform any operation/ process any element just return single enumerable collection with all element of two enumerable collections.

Union() method return the enumerable collection by eliminating the duplicate i.e just return single element if the same element exists in both enumerable collection on which union is performed.

Important point to Note

• By this fact we can say that Concat() is faster than Union() because it doesn't do any processing.

• But if after combining two collection using Concat() having single collection with too many number of duplicate element and if you want to perform further operation on that created collection takes longer time than collection created using Union() method, because Union() eliminate duplicate and create collection with less elements.

Solution 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"
                   })); 

Solution 3

In order to get the expected property names on the anonymous type you probably want to do something like:

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

and also

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

Comments

[C B]

Be careful that you don't get confused by the Concat vs Union example - as Pranay Rana mentioned, Union uses the default equality comparer. So in his example, using integers, his union and concatenation resulted in different collections. If you are using reference types, object.Equals() checks object.ReferenceEquals() and the collections will not necessarily be different. I'm not saying he's wrong, just that there is a subtlety of which to be aware when using reference types.