Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq union usage?

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.

like image 539
AliRıza Adıyahşi Avatar asked Jul 11 '12 06:07

AliRıza Adıyahşi


People also ask

What does Union do in LINQ?

In LINQ to SQL, the Union operator is defined for multisets as the unordered concatenation of the multisets (effectively the result of the UNION ALL clause in SQL).

Does LINQ Union preserve order?

Therefore, by default, PLINQ does not preserve the order of the source sequence. In this regard, PLINQ resembles LINQ to SQL, but is unlike LINQ to Objects, which does preserve ordering.

What method will return a group of elements from the given collection based on some key value?

The GroupBy() is an extension method that returns a group of elements from the given collection based on some key value.


2 Answers

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.

like image 192
Pranay Rana Avatar answered Sep 18 '22 03:09

Pranay Rana


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"
                   })); 
like image 23
RePierre Avatar answered Sep 20 '22 03:09

RePierre