Executing Queries

This document describes how you can perform various database operations using dotEntity. Make sure you have setup and mapped tables if required.

To execute queries against database for particular table, you need to call the methods of EntitySet<> generic class. The generic type parameter should be the class that maps to a particular database table.

Insert

The following example demonstrates how to insert a new Product into database.

var product = new Product(){
    ProductName = "My first product",
    Description = "This is an awesome product that you must give a try",
    IsActive = true
};
EntitySet<Product>.Insert(product);
Console.WriteLine(product.Id); //this will print the Id of newly inserted product

Tip: dotEntity automatically sets the Key property of the class after insert succeeds as is the case with Id in above example.

Update

The update operation can be performed in two ways. You can perform a direct entity update or multiple entity update.

Direct Entity Update

The following example demonstrates how to change the ProductName and IsActive fields of the Product entity we created above and calling the update method.

product.ProductName = "My first product but improved";
product.IsActive = false;
EntitySet<Product>.Update(product);
Multiple Entity Update

The following example demonstrates hwo to change the IsActive field of all the Products having Id more than 10

EntitySet<Product>.Update(new { IsActive = true}, product => product.Id > 10);

The first parameter to the Update method above is a dynamic object specifies the fields to be updated. The second parameter is an expression that specifies the condition of selection of rows to be updated.

Delete

Similar to Update above, Delete operation can also be performed in two ways. You can perform a direct entity deletion or multiple entity deletion, though caution should be taken care in case of latter.

Direct Entity Deletion

This is simplest and straightforward as shown in the example below.

EntitySet<Product>.Delete(product);

The above statement will delete the product matching from the database table.

Multiple Entity Deletion

You can specify condition to delete multiple entities at once.The following example deletes the products with Id 5 and 7.

EntitySet<Product>.Delete(product => product.Id == 5 || product.Id == 7);

Select

The select operation in dotEntity offers many options to query data from database. The method returns an IEnumerable<> of the entity type requested. The following examples show various type of select operations.

Select all the products

var products = EntitySet<Product>.Select(); //returns IEnumerable<Product> to products variable

Select products which have Id greater than 10 and less than 20

var products = EntitySet<Product>
                .Where(product => product.Id > 10 && product.Id < 20)
                .Select();

Select all the active products ordered by ProductName

var products = EntitySet<Product>
                .Where(product => product.IsActive)
                .OrderBy(product => product.ProductName)
                .Select();

Select all the inactive products ordered by Id in descending order

var products = EntitySet<Product>
                .Where(product => !product.IsActive)
                .OrderBy(product => product.Id, RowOrder.Descending)
                .Select();

Select 5 products that contain a particular string ("laptop") in ProductName

var products = EntitySet<Product>
                .Where(product => product.ProductName.Contains("laptop"))
                .OrderBy(product => product.Id)
                .Select(1, 5); //returns 5 products from 1st page

Note: When you select paginated results, an OrderBy call is a must.

Select products with specific Ids

var ids = new List<int> { 1, 3, 4, 8, 10 };
var products = EntitySet<Product>
                .Where(product => ids.Contains(product.Id))                
                .Select();

Select products except specific Ids

var ids = new List<int> { 1, 3, 4, 8, 10 };
var products = EntitySet<Product>
                .Where(product => !ids.Contains(product.Id))                
                .Select();

Count total number of matching products

var count = EntitySet<Product>
                .Where(product => product.ProductName.Contains("laptop"))
                .Count();

Get total count of matching records with paginated results

var products = EntitySet<Product>
                .Where(product => product.ProductName.Contains("laptop"))
                .OrderBy(product => product.Id)
                .SelectWithTotalMatches(out int totalRecords, 2, 15); 
                //returns 15 products from 2nd page and stores total number of matching records in totalRecords1

Note: When you select paginated results, an OrderBy call is a must.

Execute any query

Sometimes the provided CRUD operation functions are not enough to execute certain types of queries. To help with that, dotEntity provides a general Query function that executes any Sql query that you provide.

The following example demonstrates the use of Query function.

var products = EntitySet<Product>.Query("SELECT * FROM Product WHERE Id IN (SELECT ProductId FROM ProductCategory WHERE CategoryId = @CategoryId)", new {CategoryId = 5});

The above code snippet executes a complex SqlQuery and returns the matching products. Note that the parameters should be passed as a dynamic object to safeguard against any Sql Injection attacks.

Caution: You must make sure that the data returned by the query is compatible with the generic type provided to EntitySet<>