Sql Server 表创建以及Ef浅谈
1.在数据库中新建两张测试表
创建用户表
use eftest go if exists(select * from sysobjects where name='UserInfo') drop table UserInfo create table UserInfo( ID int identity(1,1) primary key, UserName nvarchar(100) not null, Sex char(1) default '男', age int ) go
创建订单表
use eftest go if exists (select * from sysobjects where name='OrderInfo') drop table OrderInfo create table OrderInfo ( ID int identity(1,1) primary key, UserID int not null , ThinName nvarchar(100) not null ) go alter table OrderInfo add constraint OrderInfo_fk_UserInfo foreign key(UserID) references UserInfo(ID) select OrderInfo_fk_UserInfo
生成的ef中的几个重要文件
以及两个相关的类
EF 基础知识：
集合版实现ef的增加、修改、删除、查询
EfTestEntities content=new EfTestEntities(); var userInfo = new UserInfo { UserName = "admin1",age = 10,Sex = "男" }; content.UserInfo.Add(userInfo); //通过主键查询记录 UserInfo userinfo=content.UserInfo.Find(13); //修改数据 userinfo.UserName = "test123"; //删除记录，请注意，在调用此方法之前，该实体必须以另一种状态存在于该上下文中。 //即：该对象不能是自己实例创建的对象，必须是先查再删 content.UserInfo.Remove(userinfo); content.SaveChanges();
状态版实现增删改查
首先是来看DbContext中的两个方法
1.传如一个实体，返回一个被跟踪的实体（为什么在上面在删除的时候，如果删除的实体没处于上下文中，就会报错，而这个状态就是在这生成的，因为增加，修改，删除的实现，并不是马上就执行的，只是你在操作的时候，先把上下文对象的状态进行了改变，在最后提交时才去按照上下文生成sql语句执行
//增加，通过更改实体的状态实现，上面集合的实现本质上也是这种是实现 context.Entry(userInfo).State = EntityState.Added; context.SaveChanges(); //修改，建议使用，因为集合必须手动建立上下文跟踪后才能操作，而这个直接通过上下下文跟踪更直接 //但这个将通过主键修改对象所有的字段，比如下面没设置age和sex，那么将改为null var useri = new UserInfo { ID = 14, UserName = "oldboy" }; context.Entry(useri).State = EntityState.Modified; context.SaveChanges(); //同理删除也一样 var useri = new UserInfo { ID = 14, UserName = "oldboy" }; context.Entry(useri).State = EntityState.Deleted; context.SaveChanges(); //状态版还能修改某一个列，而集合版修改必须的先查整个对象再修改 var useri = new UserInfo { ID = 15}; context.UserInfo.Attach(useri); //将对象加入上下文，默认为无状态（即不新增、也不修改、也不删除） context.Entry(useri).Property("UserName").CurrentValue = "yearboy"; context.Entry(useri).Property("UserName").IsModified = true; context.SaveChanges();
//状态
在DataContext中提供了Entity方法让我们来操作实体的状态
/// <summary> /// Gets a <see cref="DbEntityEntry{T}" /> object for the given entity providing access to /// information about the entity and the ability to perform actions on the entity. /// </summary> /// <typeparam name="TEntity"> The type of the entity. </typeparam> /// <param name="entity"> The entity. </param> /// <returns> An entry for the entity. </returns> public DbEntityEntry<TEntity> Entry<TEntity>(TEntity entity) where TEntity : class { Check.NotNull(entity, "entity"); return new DbEntityEntry<TEntity>(new InternalEntityEntry(InternalContext, entity)); } /// <summary> /// Gets a <see cref="DbEntityEntry" /> object for the given entity providing access to /// information about the entity and the ability to perform actions on the entity. /// </summary> /// <param name="entity"> The entity. </param> /// <returns> An entry for the entity. </returns> public DbEntityEntry Entry(object entity) { Check.NotNull(entity, "entity"); return new DbEntityEntry(new InternalEntityEntry(InternalContext, entity)); }
public virtual DbSet<TEntity> Set<TEntity>() where TEntity : class { return (DbSet<TEntity>)InternalContext.Set<TEntity>(); } /// <summary> /// Returns a non-generic <see cref="DbSet"/> instance for access to entities of the given type in the context /// and the underlying store. /// </summary> /// <param name="entityType"> The type of entity for which a set should be returned. </param> /// <returns> A set for the given entity type. </returns> /// <remarks> /// Note that Entity Framework requires that this method return the same instance each time that it is called /// for a given context instance and entity type. Also, the generic <see cref="DbSet{TEntity}"/> returned by the /// <see cref="Set"/> method must wrap the same underlying query and set of entities. These invariants must /// be maintained if this method is overridden for anything other than creating test doubles for unit testing. /// See the <see cref="DbSet"/> class for more details. /// </remarks> [SuppressMessage("Microsoft.Naming", "CA1716:IdentifiersShouldNotMatchKeywords", MessageId = "Set")] public virtual DbSet Set(Type entityType) { Check.NotNull(entityType, "entityType"); return (DbSet)InternalContext.Set(entityType); }
两种语法的查询
查询语法
DbContext context1=new EfTestEntities(); //获取UserInfo的集合，等同 EfTestEntities 中的UserInfo集合 DbSet<UserInfo> users = context1.Set<UserInfo>(); var result = from info in users where info.ID > 14 && info.age != null select info.OrderInfo; Console.Write(result.Count());
DbContext context1=new EfTestEntities(); //获取UserInfo的集合，等同 EfTestEntities 中的UserInfo集合 DbSet<UserInfo> users = context1.Set<UserInfo>(); var result = from info in context.UserInfo join orde in context.OrderInfo on info.ID equals orde.UserID select info; Console.WriteLine(result); var result1 = from info in context.UserInfo from orde in info.OrderInfo select info; Console.WriteLine(result1);
var result1 = from info in context.UserInfo from orde in info.OrderInfo select new //new 新类 { id = info.ID, name = info.UserName };
方法语法
var tt = context.UserInfo.Where(c => c.ID > 14).Select(c => new {c.ID, c.UserName});
//分页 var re = context.UserInfo. Where(c => c.ID > 10). OrderByDescending(c => c.ID) .Skip(10).Take(10) .Select(c => c);
