Linq union usage?

AliRıza Adıyahşi picture AliRıza Adıyahşi · Jul 11, 2012 · Viewed 39.7k times · Source

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.

Answer

Pranay Rana picture Pranay Rana · Jul 11, 2012

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

enter image description here

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.