EF how to avoid same data in .select

Question

I am working with Entity Framework. I am retrieving data using a query with .Where and .Select conditions.

var myData = await _dbContext.Samples.Include(i => i.Experiment)
                                     .Include(i => i.Experiment.Test)
                                     .Include(i => i.Experiment.Test.Project)
                                     .Include(i => i.Samples)
                                     .Where(i => i.Experiment.Test.Status == 3 && i.Experiment.Test.TestId == 3)
                                     .Select( e => new ExperimentCollections() {
                                     ExperimentNumber = e.Experiment.Test.ExperimentNumber,
                                     ExperimentName = e.Experiment.Test.Project.Name
                                     }).ToListAsync();

There can be multiple rows with the same ExperimentNumber and I need to avoid it from inserting into myData.

For example: in myData:

myData[0]
ExperimentNumber: 1520,
ExperimentName: ABC

myData[1]
ExperimentNumber: 1521,
ExperimentName: EFG

myData[2]
ExperimentNumber: 1520,
ExperimentName: HIJ

I need to avoid myData[2] data to be inserted into myData using Entity Framework in single single query without using foreach loop.

I tried following code with .Distinct()

var myData = await _dbContext.Samples.Include(i => i.Experiment)
                                     .Include(i => i.Experiment.Test)
                                     .Include(i => i.Experiment.Test.Project)
                                     .Include(i => i.Samples)
                                     .Where(i => i.Experiment.Test.Status == 3 && i.Experiment.Test.TestId == 3)
                                     .Select( e => new ExperimentCollections() {
                                     ExperimentNumber = e.Experiment.Test.ExperimentNumber,
                                     ExperimentName = e.Experiment.Test.Project.Name
                                     }).Distinct().ToListAsync();

And I even tried .Distinct().OrderBy(i => i.TestNumber) too.

Is it only experiment #1520 you are trying to exclude or is there some other criteria? And please post an [mcve]. — jwdonahue, Jan 18 '21 at 04:58
@jwdonahue I am excluding same ExperimentNumber data. If same ExperimentNumber data appears then it should be excluded. — Abhi Singh, Jan 18 '21 at 05:03
Does this answer your question? [How can I do SELECT UNIQUE with LINQ?](https://stackoverflow.com/questions/3519165/how-can-i-do-select-unique-with-linq) — jwdonahue, Jan 18 '21 at 05:11
@jwdonahue Yes, I need to avoid data with same ExperimentNumber — Abhi Singh, Jan 18 '21 at 05:11
Ya, we seem to have a language barrier issue. When you say "same ExperimentNumber", I want to know same as what? A number in some variable in the program or duplicates in the result. If duplicates in the result, then use the `Distinct()` method on your result. — jwdonahue, Jan 18 '21 at 05:16
@jwdonahue I tried using Distinct() but it is not working as per expectation — Abhi Singh, Jan 18 '21 at 05:17
I don't see where you posted that code. It would really help if you posted an [mcve]. Someone with better linq foo than me might come along and figure it out from your little snippet, but I am just not seeing the problem. — jwdonahue, Jan 18 '21 at 05:19
@jwdonahue I tried two experiments for Distinct(). FirstIy put it after .Select() and secondly after .Where(). But It did not work on both condition — Abhi Singh, Jan 18 '21 at 05:23
@Abhi Singh, is any update? Please check if my answer can help you. — Jack J Jun - MSFT, Jan 21 '21 at 09:20

score 0 · Answer 1 · edited Jan 18 '21 at 05:36

0

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

After Where clause use GROUP BY column_name(s).

edited Jan 18 '21 at 05:36

answered Jan 18 '21 at 05:05

MD. RAKIB HASAN

2,587
4
17
29

score 0 · Answer 2 · answered Jan 19 '21 at 02:28

You can try to use Extend method to avoid same data in the list.

 public static class Extend
    {
        public static IEnumerable<TSource> DistinctBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
        {

            HashSet<TKey> seenKeys = new HashSet<TKey>();

            foreach (TSource element in source)
            {
                if (seenKeys.Add(keySelector(element)))
                {
                    yield return element;
                }
            }
        }

    }

You can use it like the following code:

var query = data.DistinctBy(p => p.ExperimentNumber).ToList();
            foreach (var item in query)
            {
                Console.WriteLine(item.ExperimentName+" "+item.ExperimentNumber);
            }

Result:

EF how to avoid same data in .select

2 Answers2