Convert T-SQL(distinct, groupby, union) to LINQ

Question

I'm familiar with t-sql but not in LINQ, I had tried basic linq but not sure on this one. Im not sure if there's an exact equivalent in linq on my written sql statement below. Basically I want to transfer the linq result to my domain model.

The sql query are using the same view (1 sql view)

Domain Model

Public class Result
{
 Public int Key{get;set;}
 Public string Name{get;set;}
}

SQL Query

SELECT DISTINCT Name = Field1 ,ItemKey = Field2 FROM sql_view
UNION 
SELECT DISTINCT Name = Field3 ,ItemKey = Field4 FROM sql_view 
UNION 
SELECT DISTINCT Name = Field5 ,ItemKey = Field6 FROM sql_view 
UNION 
SELECT  Name = Field7 ,ItemKey = Field8 FROM sql_view

Sample Data of sql_vw

Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
1      A1     23     FF23     322  ZZ322     10  A10   
1      A1     23     FF23     322  ZZ322     21  R21
1      A1     23     FF23     322  ZZ322     31  E31
2      B2     22     PP22     331  WW331     3   A3   
2      B2     22     PP22     331  WW331     7   R7
2      B2     22     PP22     331  WW331     9   E9
2      B2     22     PP22     331  WW331     12  E12
3      C3     26     HH26     340  NN340     43  H43
3      C3     26     HH26     340  NN340     39  J39

How you are getting diff values from Name & ItemKey in the same sql_view ? — Prasad Kanaparthi, Dec 18 '13 at 02:21
I wanted them to be in 2 columns only (Itemkey and Name), Each of my select statement are using different fields (field1, field2 ). The sample data I posted was the output of sql_view, But the output of my sql query will be in 2 columns — Vincent Echavez, Dec 18 '13 at 02:25

AD.Net · Answer 1 · 2013-12-18T03:31:32.660

0

(from v in context.View
group v by new {v.Field1, v.Field2} into g
select new Result{Key = g.Key.Field1, Item = g.Key.Field2})
.Union()//more of this may be

Updated with grouping

edited Dec 18 '13 at 03:31

answered Dec 18 '13 at 02:59

AD.Net

13,352
2
28
47

I tried what you did and added distinct on the last part of the code but there's still duplicate .Union( from v3 in obj select new Result { Name = v.Field7, Key = v.Field8 } )).Distinct().ToList(); – Vincent Echavez Dec 18 '13 at 03:15
What if you add .Distinct() in each part of the query – AD.Net Dec 18 '13 at 03:22
I tried that one too, but there's still duplicate, I think It's like doing a distinct in all columns, like in sql- SELECT DISTINCT field1,field2~field8 FROM table, you will notice that my field8 in my sample data has no duplicate – Vincent Echavez Dec 18 '13 at 03:26
Is there a way we can distinct specific column only on each part of the query? – Vincent Echavez Dec 18 '13 at 03:30
Give that a try, trying to group by two columns each time – AD.Net Dec 18 '13 at 03:32
I tried it but what happen is, there are 3 rows returned on the first query(field1, field2) and all other query return only 3 rows, Is my Groupby linq correct? ((from v in obj group v by new { Name = v.field1, ItemKey= v.field2 } into vGroup select new Result { Name = vGroup.Key.Name, ItemKey = vGroup.Key.ItemKey }) – Vincent Echavez Dec 18 '13 at 04:12
What happen is that all results using Group by was based only on Field1 and Field2 (1,2,3 see my sample table). – Vincent Echavez Dec 18 '13 at 05:07
Looks to be correct, yes you can still do group by the same two columns for all rows. – AD.Net Dec 18 '13 at 14:51
I tried to group each part of the query, and each of the group just returned 3 rows, this number of rows are correct for grouping Field1 and Field2 but wrong on the other. Even if I set the specific field names like Field3 and Field4 on Group by, for me it just group base on Field1 and Field2? – Vincent Echavez Dec 19 '13 at 01:58
this how i group each part of the query, IList part2 = (from g in context group g by new { name = g.Field4, itemkey = g.Field3 } into gGroup select new Result { Name = gGroup.Key.Name, ItemKey = gGroup.Key.itemkey }).ToList(); – Vincent Echavez Dec 19 '13 at 02:02

Raphaël Althaus · Answer 2 · 2013-12-18T10:30:06.803

0

To makes things easier to read and understand, I would do something like that

var part1 = context.View.Select(m => new {Name = m.Field1, ItemKey = m.Field2});
var part2 = context.View.Select(m => new {Name = m.Field3, ItemKey = m.Field4});
var part3 = context.View.Select(m => new {Name = m.Field5, ItemKey = m.Field6});
var part4 = context.View.Select(m => new {Name = m.Field7, ItemKey = m.Field8});

var result = (part1.Union(part2).Union(part3).Union(part4)).Distinct();

edited Dec 18 '13 at 10:30

answered Dec 18 '13 at 07:10

Raphaël Althaus

59,727
6
96
122

I tried that and same thing happen, The record count of part 1 will be the record count of all query. not sure why. – Vincent Echavez Dec 18 '13 at 10:10
@VincentEchavez Well, with your sample, part1, 2, and 3 will have 3 results. part4 will have 9 results. So you should have 18 lines at the end. Edited to include part4. – Raphaël Althaus Dec 18 '13 at 10:30
I tried your code and i just use my model class and not anonymous type, the output has duplicates, – Vincent Echavez Dec 18 '13 at 11:20
@VincentEchavez yes. If you don't use an anonymous type and need Distinct(), you must implement IEquatable : see http://stackoverflow.com/questions/1365748/distinct-not-working-with-linq-to-objects – Raphaël Althaus Dec 18 '13 at 11:21
Thanks for that link, It removed the duplicates, I used IEquatable on my model class, but comparing the result of my sql query to the result of the linq, it has big difference, in linq=12rows and sqlQuery with distinct=365Rows – Vincent Echavez Dec 18 '13 at 11:52
@VincentEchavez and if you don't use distinct on your view, you have exactly same results between sql server and linq ? – Raphaël Althaus Dec 18 '13 at 12:57
nope, removing distinct on my view will display all duplicates, Maybe I did something wrong with my Linq? I really dont know what to do next – Vincent Echavez Dec 18 '13 at 15:24
@VincentEchavez I mean : do you have the same result if you do in sql : `Select * from sql_view` and if you do in linq `var result = context.View.ToList()`. Cause sometimes, you may have problems with the mapping between a sql view and a c# class – Raphaël Althaus Dec 18 '13 at 15:26
your exactly right!! when i just used ToList, It removed some data, but how can that be possible, I tried to call my web api and it has the same result with my sql view but when I call it from my controller and just directly use ToList, it removes other data – Vincent Echavez Dec 18 '13 at 15:32
@VincentEchavez you're using EF, or ? – Raphaël Althaus Dec 18 '13 at 15:33
Do i have to change the subject or title? because maybe the issue is on EF mapping? – Vincent Echavez Dec 18 '13 at 15:50
@VincentEchavez Boh, that would rather be another question... But : you're doing model first, right ? If yes, try to set each property of your View as a key. This may help. At least, look which fields are treated as keys. – Raphaël Althaus Dec 18 '13 at 15:56
Thanks for all the help and Sorry for not checking first the output of ToList, I thought it would be the same..., yes I'm doing model first, did I understand you correct that I can set multiple keys on my mapping? because what I've done so far is to set Field1 as [Key] and didn't set any [Key] on the other fields – Vincent Echavez Dec 18 '13 at 16:09
@VincentEchavez Yes you understood perfectly. And nothing to be sorry about, this is rather strange at first time, and hard to detect. – Raphaël Althaus Dec 18 '13 at 16:11
Now I set other fields with key, and part1 to part4 linq query with ToList has the same number of rows(289) compare to select * from sql_view, but comparing the rows using my sql query with distinct to linq union , It still has big difference. 11 rows on linq union and 365 rows on my sql query with union and distinct – Vincent Echavez Dec 19 '13 at 01:49
I posted another question because maybe it's an EF problem and not linq, [EF Problem](http://stackoverflow.com/questions/20675853/ef-mapping-error-duplicate-data) – Vincent Echavez Dec 19 '13 at 07:36

Convert T-SQL(distinct, groupby, union) to LINQ

2 Answers2