Think LINQ deffered execution like an non-clustered SQL View

LINQ deferred execution is when you create the query, but this is not executed until you need it. So first you tell what you need and you'll get it when is needed by calling methods like ToList, First, Single, etc.


var ctx = ...
var query = from p in ctx.products
where p.enabled == true && c.deleted == false
select p;


After calling query.ToList(), the LINQ creates the SQL, sends it to server, get the results and then creates the products list. query is an IQueryable<T> object and ToList creates an IList<T> object.

An non-clustered SQL View acts pretty same like the LINQ non deferred execution. First you define the query, then you call it or use it in any other queries.

CREATE VIEW vwProducts
AS
SELECT p.*
FROM dbo.products p
WHERE p.enabled = 1 AND p.deleted = 0


SELECT *
FROM vwProducts p
WHERE p.description like '%potatoes%'

SQL Server will expand the View vwProducts when the last query is executed.

So, how to see the first LINQ query like a view?

First thing needed is somebody to define/create it.

private IQueryable<product> createProductsView(DBContext ctx){
var query = from c in ctx.products
where p.enabled == true && c.deleted == false
select p;
return query; // recall this now the query isn't yet executed
}

Next I can use in other methods, probably in the ones used by UI

public IList<product> FindByKeyword(string keyword){
var ctx = ContextFactory.CreateContext();
var query = createProductsView(ctx);

query = from p in query // isn't executed yet the query
where p.description.Contains(keyword)
select p;

var productsList = query.ToList(); // it is now
return productsList;
}


Often views are used to simplify database queries. Imagine you can do this at a higher level.

Comments

Popular posts from this blog

IIS 7.5, HTTPS Bindings and ERR_CONNECTION_RESET

Table Per Hierarchy Inheritance with Column Discriminator and Associations used in Derived Entity Types

About the "The Controls collection cannot be modified because the control contains code blocks" symptom