How can I call distinct on entities in ASP.NET MVC 4

Question

I have two tables where users save data for cities. I want to count the total number of cities in both tables using a Distinct on the join query.

Table 1       Table 2

ID City       ID City
--------      ----------
1  CHD        1  PUT
2  PUT        2  CHD
3  LUD        3  MALK
4  CHD        4  PUT
5  PUT        5  APC

I want to join both tables and get the total count of Cities, but I want to make sure that cites will not be repeated in the count. That is, if CHD exists in table 1 and Table 2 then only count it once.

I don't want to count cities repeatedly. I want to be able to filter my results and get the total count.

I have written this code for Table 1 but I want the same result with a joined query.

public int Locations()
{
    int Locations = 0;           

    using (var db = new Entities())
        Locations = db.Table1.Select(o => o.City).Distinct().Count();          

    int Locations = 0;           

    using (var db = new Entities())
        Locations = db.Table2.Select(o => o.City).Distinct().Count();

    return Locations;
}

How can I do this with 2 tables?

Dillon · Accepted Answer · 2017-06-06 17:57:34Z

1

This is untested, but I think it might be what you're looking for.

public int Locations()
{         
     using (var db = new Entities()) 
     {
          return (db.Table1.Select(x => x.City).Union(db.Table2.Select(y => y.City)))
                 .Distinct().Count()
     }
}

edited Jun 6, 2017 at 17:57

answered Jun 6, 2017 at 17:49

Dillon

7077 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bikram Over a year ago

Thank You #Dillon its really help full.

Chris Pratt · Accepted Answer · 2017-06-06 18:01:36Z

0

Do a LINQ Union, which will automatically only take uniques from each component list:

var locations = table1Locations.Union(table2Locations);

Bear in mind that this will only work because you're selecting just the City from each table. That means you're effectively combining two IEnumerable<string>s. If you were to try to combine the objects directly it would fail, because you'd essentially have an IEnumerable<Table1Entity> and IEnumerable<Table2Entity>, which cannot be unioned.

answered Jun 6, 2017 at 18:01

Chris Pratt

240k37 gold badges414 silver badges469 bronze badges

Collectives™ on Stack Overflow

How can I call distinct on entities in ASP.NET MVC 4

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related