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.

Data Queries

In this tutorial we will take a look at writing data queries, which could help us boost the performance of fetching more than one record. The performance of data access framework depends on how you write data queries. "Database.GetList()" and "Database.Find()" methods are available to fetch records from database based on a criteria (For details on method definitions please read tutorials Database.GetList() and Database.Find()).

When using

Database.GetList(a=> [CRITERIA])

be careful to keep [CRITERIA] simple so it can be translated directly into SQL Queries. Specifying complex criteria or calculated properties directly into method lambda expressions will result in fetching all the results into the memory and then applying the criteria. Always split complex criteria by specifying it using "Where" Linq extension method. This is because a simple criterion is translated into SQL query, which runs in the database context and also enables caching of result set, as shown below:

 

If you put something complicated in the "Databae.GetList()" filter, then the data model will take all instances of your entity into memory, which can cause performance issues and memory leaks in your application


 

Also you should avoid using "||" conditional operator in lambda expression and rather should write separate data queries as the screenshot below shows:

It is better to perform separate queries rather than using "||" operator on data query criteria. This can increase the performance, because only related records are fetched into memory rather than all the records, which is also helpful in supporting data caching as we discussed in the previous lesson on performance, you can use:

M# encourages developers to use "Database.Find()" method when you want to fetch a single record based on a criteria. It is advised that you should follow the same criteria specification approach we discussed above in order to enjoy performance and data caching.

Important: "Database.Find()" method must not be confused with "Database.Get()", which also returns single record but requires "ID" of the record you want to fetch (Please read tutorial Database.Get() VS Database.Find() for more details). It is also important to remember that Database.Find() must not be used conditional statements to determine the existence of an instance rather "Database.Any() and Database.None()" method should be used (More information on these methods is explained in tutorial Database.Any(), None(), Count()).

Criterion

Criterion is M#’s internal class used to provide parameters, comparators and respective values to be searched in the database. M# translates the provided criteria parameters into SQL query very easily, which can also be used for simple queries and when data caching is required. The next example shows how to get all the employees whose email address ends with "@geeks.ltd.uk" by using a Criterion: