Entity Framework with MySQL learning Note one (query)

Reference: Http://msdn.microsoft.com/en-us/data/jj574232.aspx

EF queries are basically 3

The default is Lazy Loading

The feature is that EF will be like a database request only when data is needed, and it will not use any inner join

For example, we have a product, there are many colors, (1 to many)

So we want to pick out every product and color.

using New Efdb ()) {    var// like database request Prods, but will not include colors    foreach (var in prods)    {        var// every time like a database request color     }              }

First must use ToList (), otherwise after the prod.colors will be the error.

If there is a lot of prods, it will make many requests, which has an impact on performance!

Opened connection at the/9/ -  at: -: -+ ,:xxSELECT ' Extent1 '. ' id ', ' Extent1 '. ' Color ', ' Extent1 '. ' prod_id ' from ' prod_color ' as ' Extent1 ' WHERE ' Extent1 '. ' Prod_ Id '=@EntityKeyValue1--EntityKeyValue1:'2'(Type = Int32, isnullable =false)--Executing at the/9/ -  at: -: -+ ,:xx--Completedinch  Ams with result:efmysqldatareaderclosed connection at the/9/ -  at: -: -+ ,:xxopened connection at the/9/ -  at: -: -+ ,:xxSELECT ' Extent1 '. ' id ', ' Extent1 '. ' Color ', ' Extent1 '. ' prod_id ' from ' prod_color ' as ' Extent1 ' WHERE ' Extent1 '. ' Prod_ Id '=@EntityKeyValue1--EntityKeyValue1:'3'(Type = Int32, isnullable =false)--Executing at the/9/ -  at: -: -+ ,:xx--Completedinch  Ams with result:efmysqldatareaderclosed connection at the/9/ -  at: -: -+ ,:xxopened connection at the/9/ -  at: -: the+ ,:xxSELECT ' Extent1 '. ' id ', ' Extent1 '. ' Color ', ' Extent1 '. ' prod_id ' from ' prod_color ' as ' Extent1 ' WHERE ' Extent1 '. ' Prod_ Id '=@EntityKeyValue1--EntityKeyValue1:'4'(Type = Int32, isnullable =false)--Executing at the/9/ -  at: -: the+ ,:xx--Completedinch  Onems with result:efmysqldatareaderclosed connection at the/9/ -  at: -: the+ ,:xx

View Code

So usually we don't really like to use lazy loading

The 2nd kind is eagerly Loading.

It is mainly used inInclude 方法来调用 inner join ,使查询次数减少 

using(Efdb db =NewEfdb ()) {db. Configuration.lazyloadingenabled=false; varProds = db.prods.Include (P = p.colors). ToList ();//like database requests prods and colors//var prods = db.prods.Include (P = p.colors.select (c = c.sizes)).  ToList (); If there's sizes    foreach(varProdinchprods) {        varcolor = prod.colors;//I don't need any more requests.    }              }

Opened connection at the/9/ -  at: A: at+ ,:xxSELECT ' Project1 '. ' id ', ' Project1 '. ' Code ', ' Project1 '. ' Name ', ' Project1 '. ' C1 ', ' Project1 '. ' Id1 ', ' Project1 '. ' Color ', ' Project1 '. ' prod_id ' from (SELECT ' Extent1 '. ' id ', ' Extent1 '. ' Code ', ' Extent1 '. ' Name ', ' Extent2 '. ' id ' as ' id1 ', ' Extent2 '. ' Color ', ' Extent2 '. ' prod_id ', Case when (' Extent2 '. ' id ' was NULL) then (null) ELSE (1END as ' C1 ' from ' prod ' as ' Extent1 ' left OUTER joins ' Prod_color ' as ' Extent2 ' on ' Extent1 '. ' ID '=' Extent2 '. ' prod_id ') As ' Project1 ' ORDER by ' Project1 '. ' ID ' ASC, ' Project1 '. ' C1 ' ASC--Executing at the/9/ -  at: A: -+ ,:xx--Completedinch  -ms with result:efmysqldatareaderclosed connection at the/9/ -  at: A: -+ ,:xx

View Code

The statement is scary and the subquery is out. I think for the database personnel, this query decision is unqualified ...

The 3rd type is explicitly Loading

This is similar to the lazy loading, but it can be controlled manually.

using(Efdb db =NewEfdb ()) {db. Configuration.lazyloadingenabled=false; //var prods = db.prods.Include (p = = p.colors). ToList (); //like database requests prods and colors//var prods = db.prods.Include (P = p.colors.select (c = c.sizes)).  ToList (); If there's sizes    varProds =db.prods.ToList (); foreach(varProdinchprods) {                           varcolor = prod.colors;//NULLDb. Entry (PROD). Collection (p = p.colors). Load ();//send requests like database//db. Entry (PROD). Collection (p = p.colors). Query (). Where (c = = C.color = = "Red"). Load (); //and filter.color = prod.colors;//with the    }              }

Opened connection at the/9/ -  at: -: -+ ,:xxSELECT ' Extent1 '. ' id ', ' Extent1 '. ' Code ', ' Extent1 '. ' Name ' from ' prod ' as ' Extent1 '--Executing at the/9/ -  at: -: -+ ,:xx--Completedinch  -ms with result:efmysqldatareaderclosed connection at the/9/ -  at: -: -+ ,:xxopened connection at the/9/ -  at: -: the+ ,:xxSELECT ' Extent1 '. ' id ', ' Extent1 '. ' Color ', ' Extent1 '. ' prod_id ' from ' prod_color ' as ' Extent1 ' WHERE ' Extent1 '. ' Prod_ Id '=@EntityKeyValue1--EntityKeyValue1:'1'(Type = Int32, isnullable =false)--Executing at the/9/ -  at: -: the+ ,:xx--Completedinch  -ms with result:efmysqldatareaderclosed connection at the/9/ -  at: -: the+ ,:xxopened connection at the/9/ -  at: -: -+ ,:xxSELECT ' Extent1 '. ' id ', ' Extent1 '. ' Color ', ' Extent1 '. ' prod_id ' from ' prod_color ' as ' Extent1 ' WHERE ' Extent1 '. ' Prod_ Id '=@EntityKeyValue1--EntityKeyValue1:'2'(Type = Int32, isnullable =false)--Executing at the/9/ -  at: -: -+ ,:xx--Completedinch  -ms with result:efmysqldatareaderclosed connection at the/9/ -  at: -: -+ ,:xxopened connection at the/9/ -  at: -: -+ ,:xxSELECT ' Extent1 '. ' id ', ' Extent1 '. ' Color ', ' Extent1 '. ' prod_id ' from ' prod_color ' as ' Extent1 ' WHERE ' Extent1 '. ' Prod_ Id '=@EntityKeyValue1--EntityKeyValue1:'3'(Type = Int32, isnullable =false)--Executing at the/9/ -  at: -: -+ ,:xx--Completedinch  -ms with result:efmysqldatareaderclosed connection at the/9/ -  at: -: -+ ,:xxopened connection at the/9/ -  at: -: -+ ,:xxSELECT ' Extent1 '. ' id ', ' Extent1 '. ' Color ', ' Extent1 '. ' prod_id ' from ' prod_color ' as ' Extent1 ' WHERE ' Extent1 '. ' Prod_ Id '=@EntityKeyValue1--EntityKeyValue1:'4'(Type = Int32, isnullable =false)--Executing at the/9/ -  at: -: -+ ,:xx--Completedinch  -ms with result:efmysqldatareaderclosed connection at the/9/ -  at: -: -+ ,:xx

View Code

Also used a lot of queries ...

Currently I have not found a more acceptable way to query. At least I think people who have a bit of a performance requirement should not use any of the above methods.

Continue to specialize in ... Cond

Entity Framework with MySQL learning Note one (query)