General Tips on Entity Framework v1 & Linq to Entities: ToTraceString() If you need to know the underlying SQL that the EF generates for a Linq To Entities query, then use the ToTraceString() method of the ObjectQuery class. (or use LINQPAD) Note that you need to cast the LINQToEntities query to ObjectQuery before calling TotraceString() as follows: 1: string efSQL = ((ObjectQuery)from c in ctx.Contact
2: where c.Address.Any(a => a.CountryRegion == "US")
3: select c.ContactID).ToTraceString();
================================================================================
MARS or MultipleActiveResultSet
When you create a EDM Model (EDMX file) from the database using Visual Studio, it generates a connection string with the same name as the name of the EntityContainer in CSDL. In the ConnectionString so generated it sets the MultipleActiveResultSet attribute to true by default. So if you are running the following query then it streams multiple readers over the same connection:
1: using (BAEntities context = new BAEntities())
2: {
3: var cons =
4: from con in context.Contacts
5: where con.FirstName == "Jose"
6: select con;
7: foreach (var c in cons)
8: {
9: if (c.AddDate < new System.DateTime(2007, 1, 1))
10: {
11: c.Addresses.Load();
12: }
13: }
14: }
=================================================================================
Explicitly opening and closing EntityConnection
When you call ToList() or foreach on a LINQToEntities query the EF automatically closes the connection after all the records from the query have been consumed.
Thus if you need to run many LINQToEntities queries over the same connection then explicitly open and close the connection as follows:
1: using (BAEntities context = new BAEntities())
2: {
3: context.Connection.Open();
4: var cons = from con in context.Contacts where con.FirstName == "Jose"
5: select con;
6: var conList = cons.ToList();
7: var allCustomers = from con in context.Contacts.OfType<Customer>()
8: select con;
9: var allcustList = allCustomers.ToList();
10: context.Connection.Close();
11: }
======================================================================
Dispose ObjectContext only if required
After you retrieve entities using the ObjectContext and you are not explicitly disposing the ObjectContext then insure that your code does consume all the records from the LinqToEntities query by calling .ToList() or foreach statement, otherwise the the database connection will remain open and will be closed by the garbage collector when it gets to dispose the ObjectContext.
Secondly if you are making updates to the entities retrieved using LinqToEntities then insure that you dont inadverdently dispose of the ObjectContext after the entities are retrieved and before calling .SaveChanges() since you need the SAME ObjectContext to keep track of changes made to the Entities (by using ObjectStateEntry objects). So if you do need to explicitly dispose of the ObjectContext do so only after calling SaveChanges() and only if you dont need to change track the entities retrieved any further.
=======================================================================
SQL InjectionAttacks under control with EFv1
LinqToEntities and LinqToSQL queries are parameterized before they are sent to the DB hence they are not vulnerable to SQL Injection attacks.
EntitySQL may be slightly vulnerable to attacks since it does not use parameterized queries. However since the EntitySQL demands that the query be valid Entity SQL syntax and valid native SQL syntax at the same time.
So the only way one can do a SQLInjection Attack is by knowing the SSDL of the EDM Model and be able to write the correct EntitySQL (note one cannot append regular SQL since then the query wont be a valid EntitySQL syntax) and append it to a parameter.
======================================================================
Improving Performance
You can convert the EntitySets and AssociationSets in a EDM Model into precompiled Views using the edmgen utility. for e.g. the Customer Entity can be converted into a precompiled view using edmgen and all LinqToEntities query against the contaxt.Customer EntitySet will use the precompiled View instead of the EntitySet itself (the same being true for relationships (EntityReference & EntityCollections of a Entity)). The advantage being that when using precompiled views the performance will be much better.
The syntax for generating precompiled views for a existing EF project is :
edmgen /mode:ViewGeneration /inssdl:BAModel.ssdl /incsdl:BAModel.csdl /inmsl:BAModel.msl /p:Chap14.csproj
Note that this will only generate precompiled views for EntitySets and Associations and not for existing LinqToEntities queries in the project.(for that use CompiledQuery.Compile<>)
Secondly if you have a LinqToEntities query that you need to run multiple times, then one should precompile the query using CompiledQuery.Compile method. The CompiledQuery.Compile<> method accepts a lamda expression as a parameter, which denotes the LinqToEntities query that you need to precompile.
The following is a example of a lamda that we can pass into the CompiledQuery.Compile() method
1: Expression<Func<BAEntities, string, IQueryable<Customer>>> expr = (BAEntities ctx1, string loc) =>
2: from c in ctx1.Contacts.OfType<Customer>()
3: where c.Reservations.Any(r => r.Trip.Destination.DestinationName == loc)
4: select c;
Then we call the Compile Query as follows:
1: var query = CompiledQuery.Compile<BAEntities, string, IQueryable<Customer>>(expr);
2:
3: using (BAEntities ctx = new BAEntities())
4: {
5: var loc = "Malta";
6: IQueryable<Customer> custs = query.Invoke(ctx, loc);
7: var custlist = custs.ToList();
8: foreach (var item in custlist)
9: {
10: Console.WriteLine(item.FullName);
11: }
12: }
Note that if you created a ObjectQuery or a Enitity SQL query instead of the LINQToEntities query, you dont need precompilation for e.g.
1: An Example of EntitySQL query :
2: string esql = "SELECT VALUE c from Contacts AS c where c is of(BAGA.Customer) and c.LastName = 'Gupta'";
3: ObjectQuery<Customer> custs = CreateQuery<Customer>(esql);
1: An Example of ObjectQuery built using ObjectBuilder methods:
2: from c in Contacts.OfType<Customer>().Where("it.LastName == 'Gupta'")
3: select c
This is since the Query plan is cached and thus the performance improves a bit, however since the ObjectQuery or EntitySQL query still needs to materialize the results into Entities hence it will take the same amount of performance hit as with LinqToEntities.
However note that not ALL EntitySQL based or QueryBuilder based ObjectQuery plans are cached. So if you are in doubt always create a LinqToEntities compiled query and use that instead
============================================================
GetObjectStateEntry Versus GetObjectByKey
We can get to the Entity being referenced by the ObjectStateEntry via its Entity property and there are helper methods in the ObjectStateManager (osm.TryGetObjectStateEntry) to get the ObjectStateEntry for a entity (for which we know the EntityKey). Similarly The ObjectContext has helper methods to get an Entity i.e. TryGetObjectByKey().
TryGetObjectByKey() uses GetObjectStateEntry method under the covers to find the object, however One important difference between these 2 methods is that TryGetObjectByKey queries the database if it is unable to find the object in the context, whereas TryGetObjectStateEntry only looks in the context for existing entries. It will not make a trip to the database
=============================================================
POCO objects with EFv1:
To create POCO objects that can be used with EFv1. We need to implement 3 key interfaces:
IEntityWithKey
IEntityWithRelationships
IEntityWithChangeTracker
Implementing IEntityWithKey is not mandatory, but if you dont then we need to explicitly provide values for the EntityKey for various functions (for e.g. the functions needed to implement IEntityWithChangeTracker and IEntityWithRelationships).
Implementation of IEntityWithKey involves exposing a property named EntityKey which returns a EntityKey object.
Implementation of IEntityWithChangeTracker involves implementing a method named SetChangeTracker since there can be multiple changetrackers (Object Contexts) existing in memory at the same time.
1: public void SetChangeTracker(IEntityChangeTracker changeTracker)
2: {
3: _changeTracker = changeTracker;
4: }
Additionally each property in the POCO object needs to notify the changetracker (objContext) that it is updating itself by calling the EntityMemberChanged and EntityMemberChanging methods on the changeTracker. for e.g.:
1: public EntityKey EntityKey
2: {
3: get { return _entityKey; }
4: set
5: {
6: if (_changeTracker != null)
7: {
8: _changeTracker.EntityMemberChanging("EntityKey");
9: _entityKey = value;
10: _changeTracker.EntityMemberChanged("EntityKey");
11: }
12: else
13: _entityKey = value;
14: }
15: }
16: ===================== Custom Property ====================================
17:
18: [EdmScalarPropertyAttribute(IsNullable = false)]
19: public System.DateTime OrderDate
20: {
21: get { return _orderDate; }
22: set
23: {
24: if (_changeTracker != null)
25: {
26: _changeTracker.EntityMemberChanging("OrderDate");
27: _orderDate = value;
28: _changeTracker.EntityMemberChanged("OrderDate");
29: }
30: else
31: _orderDate = value;
32: }
33: }
Finally you also need to create the EntityState property as follows:
1: public EntityState EntityState
2: {
3: get { return _changeTracker.EntityState; }
4: }
The IEntityWithRelationships involves creating a property that returns RelationshipManager object:
1: public RelationshipManager RelationshipManager
2: {
3: get
4: {
5: if (_relManager == null)
6: _relManager = RelationshipManager.Create(this);
7: return _relManager;
8: }
9: }
============================================================
Tip : ProviderManifestToken – change EDMX File to use SQL 2008 instead of SQL 2005
To use with SQL Server 2008, edit the EDMX file (the raw XML) changing the ProviderManifestToken in the SSDL attributes from "2005" to "2008"
=============================================================
With EFv1 we cannot use Structs to replace a anonymous Type while doing projections in a LINQ to Entities query. While the same is supported with LINQToSQL, it is not with LinqToEntities. For e.g. the following is not supported with LinqToEntities since only parameterless constructors and initializers are supported in LINQ to Entities. (the same works with LINQToSQL)
1: public struct CompanyInfo
2: {
3: public int ID { get; set; }
4: public string Name { get; set; }
5: }
6: var companies = (from c in dc.Companies
7: where c.CompanyIcon == null
8: select new CompanyInfo { Name = c.CompanyName, ID = c.CompanyId }).ToList(); ;
