Advanced Queries

dotEntity helps to you to perform some complex database operations with ease. You can perform Joins, define Relations between entities, perform database migrations and so forth.

Insert multiple entities at once

You can insert multiple entities to the database in one go. The following code snippet demonstrates how to insert multiple products at once.

var product1 = new Product()
    ProductName = "Laptop",
    ProductDescription = "Some description about the laptop.",
    DateCreated = DateTime.Now,
    Price = 1000

var product2 = new Product()
    ProductName = "Tablet",
    ProductDescription = "A very small and powerful device for you",
    DateCreated = DateTime.Now,
    Price = 280

var product3 = new Product()
    ProductName = "iPod",
    ProductDescription = "Your favorite music on the go",
    DateCreated = DateTime.Now,
    Price = 200

EntitySet<Product>.Insert(new[] { product1, product2, product3 }); //pass multiple products as an Array

Execute Multiple Queries at once

Sometimes you'd want to execute multiple sql queries fetching diversified type of data in one go. dotEntity provides you with MultiResult. Because multiple queries can work on different tables at once, we need to use EntitySet (the non generic class) instead of our usual 'EntitySet<>` generic class.

The following example shows how you can perform multiple queries simultaneously in a single connection call.

 using (var result = EntitySet.Query(@"SELECT * FROM Product
                        SELECT COUNT(*) FROM Product", null /* the parameter if any, null because there are no*/))
                result.SelectAs<Product>(); //gets result of first query
                result.SelectScalerAs<int>(); //gets result of second query

Note: The multi query should be used within using block to dispose the reader and connection.

Perform Join between two or more tables

The join between two tables makes the use of two functions, Join that performs the join operation and Relate that relates entities with each other. The following example demonstrates how you can perform a join multiple entities easily in dotEntity

 var products = EntitySet<Product>
                .Where(x => x.ProductName.Contains("laptop"))
                .Join<ProductCategory>("Id", "ProductId", joinType: JoinType.LeftOuter)
                .Join<Category>("CategoryId", "Id", joinType: JoinType.LeftOuter)
                .Relate<Category>((product, category) =>
                   if(product.Categories == null)
                        product.Categories = new List<Category>();

The select query in case of a join is performed by calling SelectNested function.