Entity Framework入门到精通（二）-基本操作
EF 提供了一个类似 LINQ to SQL 那样的 IDE 可视化设计界面，但似乎是个半成品，功能弱且有些错误，但愿下个版本有所改进。
1. ObjectContext
ObjectContext 对应 LINQ to SQL 的 DataContext，我们通过它来管理数据库连接、实体对象查询更新等。
public class ObjectContext : IDisposable
{
// Events
public event EventHandler SavingChanges;
// Methods
public ObjectContext(EntityConnection connection);
public ObjectContext(string connectionString);
private ObjectContext(EntityConnection connection, bool isConnectionConstructor);
protected ObjectContext(EntityConnection connection, string defaultContainerName);
protected ObjectContext(string connectionString, string defaultContainerName);
public void AcceptAllChanges();
public void AddObject(string entitySetName, object entity);
public void ApplyPropertyChanges(string entitySetName, object changed);
public void Attach(IEntityWithKey entity);
public void AttachTo(string entitySetName, object entity);
public EntityKey CreateEntityKey(string entitySetName, object entity);
public ObjectQuery<T> CreateQuery<T>(string queryString, params ObjectParameter[] parameters);
public void DeleteObject(object entity);
public void Detach(object entity);
public void Dispose();
public object GetObjectByKey(EntityKey key);
public void Refresh(RefreshMode refreshMode, IEnumerable collection);
public void Refresh(RefreshMode refreshMode, object entity);
public int SaveChanges();
public int SaveChanges(bool acceptChangesDuringSave);
public bool TryGetObjectByKey(EntityKey key, out object value);
// Properties
public int? CommandTimeout { get; set; }
public DbConnection Connection { get; }
public string DefaultContainerName { get; set; }
public MetadataWorkspace MetadataWorkspace { get; }
public ObjectStateManager ObjectStateManager { get; }
}
public partial class TestEntities : ObjectContext
{
public ObjectQuery<User> User
{
get
{
if ((this._User == null))
{
this._User = base.CreateQuery<User>("[User]");
}
return this._User;
}
}
private ObjectQuery<User> _User;
public void AddToUser(User user)
{
base.AddObject("User", user);
}
}
使用演示
using (var context = new TestEntities())
{
context.Connection.StateChange += (s, e) => Console.WriteLine("{0} -> {1}", e.OriginalState, e.CurrentState);
context.Connection.Disposed += (s, e) => Console.WriteLine("Dispose...");
context.Connection.Open();
}
2. EntityObject
EF 总算是补齐了序列化特性，省得我们自己去折腾了。
[EdmEntityType(NamespaceName = "TestModel", Name = "User")]
[DataContract(IsReference = true)]
[Serializable()]
public partial class User : EntityObject
{
public static User CreateUser(int id, string name)
{
User user = new User();
user.Id = id;
user.Name = name;
return user;
}
[EdmScalarProperty(EntityKeyProperty = true, IsNullable = false)]
[DataMember]
public int Id
{
get
{
return this._Id;
}
set
{
this.OnIdChanging(value);
this.ReportPropertyChanging("Id");
this._Id = StructuralObject.SetValidValue(value);
this.ReportPropertyChanged("Id");
this.OnIdChanged();
}
}
private int _Id;
partial void OnIdChanging(int value);
partial void OnIdChanged();
[EdmScalarProperty(IsNullable = false)]
[DataMember]
public string Name
{
get
{
return this._Name;
}
set
{
this.OnNameChanging(value);
this.ReportPropertyChanging("Name");
this._Name = StructuralObject.SetValidValue(value, false);
this.ReportPropertyChanged("Name");
this.OnNameChanged();
}
}
private string _Name;
partial void OnNameChanging(string value);
partial void OnNameChanged();
[EdmRelationshipNavigationProperty("TestModel", "FK_Order_User", "Order")]
[XmlIgnore]
[SoapIgnore]
[DataMember]
public EntityCollection<Order> Order
{
get
{
return ((IEntityWithRelationships)(this)).RelationshipManager.
GetRelatedCollection<Order>("TestModel.FK_Order_User", "Order");
}
set
{
if ((value != null))
{
((IEntityWithRelationships)(this)).RelationshipManager.
InitializeRelatedCollection<Order>("TestModel.FK_Order_User", "Order", value);
}
}
}
}
3. LINQ to Entities
这个最易上手，基本上和 LINQ to SQL 没啥区别。
using (var context = new TestEntities())
{
var user = (from u in context.User where u.Name == "user1" select u).FirstOrDefault();
Console.WriteLine(user.Name);
var users = from u in context.User where u.Age > 0 select new { u.Id, u.Name };
foreach (var item in users)
{
Console.WriteLine(item.Name);
}
}
4. Entity SQL
这是很多 LINQ to SQL 用户所期望的，一种类似 T-SQL 且面向对象查询语言。
using (var context = new TestEntities())
{
var sql = "SELECT VALUE u FROM TestEntities.User AS u WHERE u.Name = @name";
var user = context.CreateQuery<User>(sql, new ObjectParameter("name", "user1"));
foreach (var item in user)
{
Console.WriteLine(item.Name);
}
}
这种参数化的查询方式有点像我们熟悉的 DbParameter。
Entity SQL 的语法示意图。
我们也可以查询某几个属性值，但返回的不是匿名类型，而是 System.Data.Common.DbDataRecord。
using (var context = new TestEntities())
{
var sql = "SELECT u.Id, u.Name FROM TestEntities.User AS u WHERE u.Name = @name";
var reader = context.CreateQuery<DbDataRecord>(sql, new ObjectParameter("name", "user1"));
foreach (var item in reader)
{
Console.WriteLine(item["name"]);
}
}
如果只是返回单个属性，别忘了 "VALUE" 。
var sql = "SELECT VALUE u.Name FROM TestEntities.User AS u WHERE u.Name = @name";
var reader = context.CreateQuery<string>(sql, new ObjectParameter("name", "user1"));
foreach (var item in reader)
{
Console.WriteLine(item);
}
5. Method-Based Syntax
用相关方法实现 Chaining Queries，分为 LINQ Method-Based Queries 和 ObjectQuery's Query Builder Methods 两种。
using (var context = new TestEntities())
{
var user = context.User.Where(u => u.Name == "user1").Select(u => new { u.Id, u.Name }).First();
Console.WriteLine(user.Name);
// ---------------------
var users = context.User.
Where("it.Name = @name", new ObjectParameter("name", "user1")).
Select("it.Name, it.Age").
Top("@num", new ObjectParameter("num", 2));
foreach (var item in users)
{
Console.WriteLine(item["name"]);
}
}
ObjectQuery<T> 提供了这些基于 Entity SQL 字符串的查询方式。
public class ObjectQuery<T> : ObjectQuery, ...
{
public ObjectQuery<DbDataRecord> GroupBy(string keys, string projection, params ...);
public ObjectQuery<T> Include(string path);
public ObjectQuery<T> Intersect(ObjectQuery<T> query);
public ObjectQuery<TResultType> OfType<TResultType>();
public ObjectQuery<T> OrderBy(string keys, params ObjectParameter[] parameters);
public ObjectQuery<DbDataRecord> Select(string projection, params ObjectParameter[] parameters);
public ObjectQuery<TResultType> SelectValue<TResultType>(string projection, params ...);
public ObjectQuery<T> Skip(string keys, string count, params ObjectParameter[] parameters);
IEnumerator<T> IEnumerable<T>.GetEnumerator();
public ObjectQuery<T> Top(string count, params ObjectParameter[] parameters);
public ObjectQuery<T> Union(ObjectQuery<T> query);
public ObjectQuery<T> UnionAll(ObjectQuery<T> query);
public ObjectQuery<T> Where(string predicate, params ObjectParameter[] parameters);
private string _name;
private const string DefaultName = "it";
public string Name { get; set; }
}
6. EntityClient
这种方式非常类似传统 ADO.NET 操作。
using (var conn = new EntityConnection("name=TestEntities"))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT VALUE u FROM TestEntities.User AS u WHERE u.Name == @name";
cmd.Parameters.Add(new EntityParameter { ParameterName = "name", Value = "user1" });
using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
Console.WriteLine(reader["Name"]);
}
}
}
7. Add
注意 ObjectContext 用 ObjectQuery<T> 返回 EntitySet，添加实体对象实际通过 ObjectContext.AddObject() 完成的。
using (var context = new TestEntities())
{
var user = new User { Name = "user3", Age = 23 };
context.AddToUser(user);
context.SaveChanges();
}
8. Update
using (var context = new TestEntities())
{
var user = context.User.Where(u => u.Name == "user1").FirstOrDefault();
user.Age += 2;
context.SaveChanges();
}
9. Delete
using (var context = new TestEntities())
{
var user = context.User.Where(u => u.Name == "user2").FirstOrDefault();
context.DeleteObject(user);
context.SaveChanges();
}
10. Refresh
ObjectContext.Refresh() 允许我们自行决定刷新策略，包括数据库优先或者本地修改优先。
using (var context = new TestEntities())
{
var user = context.User.Where(u => u.Name == "user1").FirstOrDefault();
user.Age += 13;
var age = user.Age;
context.Refresh(RefreshMode.StoreWins, user);
Console.WriteLine("{0}, {1}", user.Age, age);
}
11. ToTraceString
ObjectContext 并没有提供 LINQ to SQL DataContext.Log 这样的功能，要查看实际生成的 T-SQL 语句，要么借助 SQL Server Sql Profiler 这样的工具，要么使用 ObjectQuery.ToTraceString() 方法。
using (var context = new TestEntities())
{
var users1 = context.User.Where(u => u.Name == "user1");
Console.WriteLine((users1 as ObjectQuery).ToTraceString());
var users2 = from u in context.User where u.Name == "user1" select u;
Console.WriteLine((users2 as ObjectQuery).ToTraceString());
var sql = "SELECT VALUE u FROM TestEntities.User AS u WHERE u.Name = 'user1'";
var users3 = context.CreateQuery<User>(sql);
Console.WriteLine(users3.ToTraceString())
}
输出:
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age] FROM [dbo].[User] AS [Extent1] WHERE N'user1' = [Extent1].[Name] SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age] FROM [dbo].[User] AS [Extent1] WHERE N'user1' = [Extent1].[Name] SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age] FROM [dbo].[User] AS [Extent1] WHERE [Extent1].[Name] = 'user1'
12. Connection.Open
当执行多个操作时，Context 默认情况下会多次打开和关闭数据库连接，可能会造成一定的性能问题。
using (var context = new TestEntities())
{
context.Connection.StateChange += (s, e) => Console.WriteLine("{0} -> {1}", e.OriginalState, e.CurrentState);
context.Connection.Disposed += (s, e) => Console.WriteLine("Dispose...");
var user = context.User.First();
Console.WriteLine(user.Age);
var order = context.Order.First();
Console.WriteLine(order.Id);
}
输出:
Closed -> Open Open -> Closed 23 Closed -> Open Open -> Closed 1 Dispose...
我们可以显示打开数据库连接来避免这样的状况。
using (var context = new TestEntities())
{
context.Connection.StateChange += (s, e) => Console.WriteLine("{0} -> {1}", e.OriginalState, e.CurrentState);
context.Connection.Disposed += (s, e) => Console.WriteLine("Dispose...");
context.Connection.Open();
var user = context.User.First();
Console.WriteLine(user.Age);
var order = context.Order.First();
Console.WriteLine(order.Id);
}
输出:
Closed -> Open 23 1 Open -> Closed Dispose...
13. ExecuteCommand
ObjectContext 并没有提供 ExecuteCommand 之类执行原生 T-SQL 的方法，虽然可以用存储过程映射来解决，但总归是少了些什么。某些时候可能因为性能等原因，需要执行一些特殊的操作，那么下面的代码可能帮上一些忙。
using (var context = new TestEntities())
{
var bindingFlags = BindingFlags.Instance | BindingFlags.NonPublic;
var factoryProperty = typeof(EntityConnection).GetProperty("StoreProviderFactory", bindingFlags);
var factory = factoryProperty.GetValue(context.Connection, null) as DbProviderFactory;
var connStr = (context.Connection as EntityConnection).StoreConnection.ConnectionString;
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = connStr;
conn.Open();
var cmd = factory.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "select count(*) from [user]";
Console.WriteLine(cmd.ExecuteScalar());
}
}
============ 欢迎各位老板打赏~ ===========
与本文相关的文章
