M# Tutorials

Learn to build .NET applications with M#. Our step-by-step tutorials will get you up-to-speed rapidly.
If you already know ASP.NET and C#, you can master M# in a week or two.

Database.Get() VS Database.Find()

In this lesson, we will discuss two methods within M# Data access repository. These methods are used to fetch a single table record from the database. For this lesson we will use our "Employee" entity, which inherits the "User" entity, to discuss these methods in detail.

Database.Get()

This method has four overloads, requires an "Entity Type" to retrieve a record from and an "ID" to identify the record. This method returns single instance of the provided Entity Type and throws "System.ArgumentNullException", if the input parameter is null or empty.

Note: The Entity Type must implement the "IEntity" Interface. (For more information in M# Interface, please read lesson IEntity Interface, in chapter 3.)

Below are the screenshots of the four overloads for this method:

This method must only be used when single record is required from database based on the "ID" of the entity. This method also boosts performance because M# runs the query in SQL environment.

Database.Find()

This method has 2 overloads. You also need to specify the Entity Type in order to utilize this method. This method returns First matched record of the provided Entity Type if found and returns "null" if no record is available in database based on the provided criteria. Below are the screenshots of two overloads for this method:

Overload 1

This overload requires one parameter of "Criterion" type collection. Criterion is M#’s internal class used to provide parameters and respective values to be searched against the database . M# translates the provided criteria parameters into SQL query.

Example:

We can call this method to get a record of Employee by "Name", as shown below

The code shown above will return an instance of Employee entity type if there is an employee with name "John" or return "null" if no such employee exists in the database.

Important: Please note that this method takes the first record found based on the criteria provided for searching a record e.g. in case of above shown query, if there are more than one "Employees" with the name of "John" then the first record will be return by this method.

Overload 2

This overload requires two parameters and is very useful when you not only want to search a record but also want to perform sorting, or ranging before fetching a record in SQL server. The first parameter requires a "Lambda Expression" criteria and the second parameter "Query Option (M#’s internal class)" is used to pass additional query options. e.g. sorting, ranging etc.

Example

We can follow the same example as we used for the first overload but this time we will use a Lambda Expression and Query Option as shown below:

The code shown above will return an instance of Employee entity type if there is an employee having name as "John" but this time all the Employee records will be ordered by their "Email" before taking the first matching record. M# translates the method shown above in SQL server as shown below:

Key differences

Database.Get() Methods only acts on the "ID" property of the entity instance and assumes that there is a record in the database associated with the provided "ID". So, this method should always be called when you are sure about the passing "ID".

Database.Find() method can be used in many scenarios where you want to search a record by a criteria other than the "ID" of the record and want to perform additional query options e.g. sorting.

 

Database.Get() method criteria is always translated into SQL query which runs in SQL server context.

Database.Find() method criteria is not always fully translated into SQL query, hence does not always run the full query in SQL server environment e.g. If your query criteria is based on a collection the full list of records will be taken to memory and the searching will be performed in memory.