LINQ Distinct: Use Entity Framework in C# to Find Unique Records

Microsoft Entity Framework (EF) is a powerful tool for data modeling. EF works with LINQ to query data using class objects. These class objects are basically classes that map to your database tables. Instead of flipping back and forth between coding your database and coding your GUI, you can use LINQ to query your objects and return an EF entity.

New to Entity Framework? Learn more about EF at Udemy.com

Your Data Model

Visual Studio has a data modeler and GUI for creating a layout and code for your database. If you already have a database in SQL Server, you can import the table structures directly into your code. Visual Studio has a wizard that even creates the classes for you, so you don’t need to write a single line of code for your data model.

The best way to manage your data model is to create a “Data” folder in your Visual Studio solution. Right-click the folder and select “Add.” Choose “Data” in the list of types and then choose “ADO.NET Entity Data Model.” Type a name for your data model and click “Add.” A wizard will also walk you through the creation of your connection to the database and ask you what to name your entities. These values are stored in the web.config file. Visual Studio creates a “.edmx” file for you. Right-click this file and import the SQL database into your model.

After you create your model, you’ll notice you have several classes that match the name of your database tables. These are your “entities.” These entities represent your tables, and you use them to select, delete, update and insert data.

Learn how to work with MVC C# and LINQ from scratch at Udemy.com

Creating LINQ Queries

Now that you have your data set up, you can use LINQ to retrieve data. You first need to establish a “connection” to your entities. You instantiate your class to create a connection to your data models. For instance, the following code instantiates the class for the “mydb” entities:

mydbEntities data = new mydbEntities();

In the above code, the entities are instantiated and assigned to the “data” variable. The next step is deciding what you want to query and what type of object you want to return. You can return any value from your database, or you can return a data set in the form of a class model.

For instance, suppose you want to query a list of users. You would obviously return a data set in the form of the “Users” table. The following code would return a list of users with a first name of “Jane” using LINQ:

Users user = (from u in data.Users where u.fname == ‘Jane’ select u );

Now, what if you want to join a “Roles” table? The following code joins the Roles table in your results:

Users user = (from u in data.Users join r in data.Roles on u.userid equals r.userid where u.fname == ‘Jane’ select u );

Now, the issue with the above statement is that you will get several rows of the same user if the user is a part of multiple roles. That’s where the “distinct” keyword comes into play. Distinct limits the values to only distinct, unique values. Using distinct, you can get one user for each record instead of getting multiple rows based on the same user in multiple roles.

The following code is an example of the distinct keyword:

Users user = (from u in data.Users join r in data.Roles on u.userid equals r.userid where u.fname == ‘Jane’ select u ).Distinct();

That’s it. That’s all it takes to get a unique, distinct number of rows.

Working with Enumerated Objects

You can also use LINQ and the distinct keyword to work with an “IEnumerable” object. You can work with similar lambda expressions when you work with these enumerated objects. Typically, you have several values that you need to filter from. You retrieve a list of values and then loop through each of these values using the foreach loop structure.

The following is an example of retrieve a list of names from an enumerated “Names” object in an XML file:

var names = doc.Elements(“customers”).Where(x => x.FirstName == ‘Jane’).Distinct();

In this example, only distinct customers with the name “Jane” are returned. Now that you have your list of customers, you can loop through them using the foreach loop in C#. The following is an example of using the C# foreach loop:

foreach(var name in names)
{
Console.WriteLine(“Customer name is “+ name.Name);
}

This is just a few ways to use the LINQ language. LINQ is a powerful tool that lets you skip the database stored procedures and coding and pull data directly from your entities. LINQ also saves you a lot of time, because you don’t need to keep two windows open: your Visual Studio and SQL Server Management Studio.

You can also connect to your Visual Studio application to the SQL Server. This is helpful when you’re not sure how your database is set up. You might also still want to use stored procedures with your application. If that’s the case, you’ll need to connect to your SQL Server and work with your stored procedure code regardless of using LINQ. However, LINQ can also make managing large lists and enumerated objects much easier. Instead of looping through every object, you use LINQ and then loop through only the objects you need to work with.

Learn how to work with C# at an advanced level at Udemy.com

With Entity Framework and LINQ, you can create quick applications that follow the latest trends in software development in C#. EF and LINQ are commonly used in MVC C# applications, so you’ll need to know how to work with these two technologies when you work in the industry.