public List<MessageContract> Find() { using (EFTestContext context = new EFTestContext()) { return context.Message.ToList(); } }
public List<AccountContract> Find() { using (EFTestContext context = new EFTestContext()) { return context.Account.ToList(); } }
public AccountContract Find(long id) { using (EFTestContext context = new EFTestContext()) { return context.Account.FirstOrDefault(t => t.AccountId == id); } }
private static void Do(int index) { //Console.WriteLine(index); //若DB未建立,则将EFTestContext构造的注释去除,即可建立DB。 //DB连接字符串,我这里简单处理,写在EFTestContext,应该写在本项目的appSettings中 using (EFTestContext context = new EFTestContext()) { var msg = new MessageContract() { AccountId = 1, Title = "title" + index, Content = "content" + index }; context.Message.Add(msg); context.SaveChanges(); } }
private static void Main() { //若DB未建立,则将EFTestContext构造的注释去除,即可建立DB。 //DB连接字符串,我这里简单处理,写在EFTestContext,应该写在本项目的appSettings中 using (EFTestContext context = new EFTestContext()) { var account = context.Account.FirstOrDefault(t => t.AccountId == 1); //查询账号,延迟加载消息表(Message暂时不查询,需要用到时再自动查询出来) var msg = account.Messages.First(); //查询消息 #region 转换出的sql //只查询账号 //SELECT TOP (1) //[Extent1].[AID] AS [AID], //[Extent1].[aKey] AS [aKey] //FROM [dbo].[TCBaseAccount] AS [Extent1] //WHERE 1 = [Extent1].[AID] //需要用到Message时再自动查询出来 //exec sp_executesql N'SELECT //[Extent1].[MID] AS [MID], //[Extent1].[MAID] AS [MAID], //[Extent1].[mTitle] AS [mTitle], //[Extent1].[mContent] AS [mContent] //FROM [dbo].[TCBaseMessage] AS [Extent1] //WHERE [Extent1].[MAID] = @EntityKeyValue1',N'@EntityKeyValue1 bigint',@EntityKeyValue1=1 #endregion //msg.Title = "zzzzz"; //context.SaveChanges(); #region EF只更新Title字段,还是蛮聪明的 //exec sp_executesql N'UPDATE [dbo].[TCBaseMessage] //SET [mTitle] = @0 //WHERE ([MID] = @1) //',N'@0 nvarchar(256),@1 bigint',@0=N'zzzzz',@1=1 #endregion //var account = context.Account.Include(t => t.Messages).FirstOrDefault(t => t.AccountId == 1); //查询账号并指定附带查询出该账号所属的消息,立即查询(不延迟加载) #region 转换出的sql,虽然丑了点,但是相信sqlserver 会优化的,具体未知。 //SELECT [Project2].[C1] AS [C1], // [Project2].[AID] AS [AID], // [Project2].[aKey] AS [aKey], // [Project2].[C2] AS [C2], // [Project2].[MID] AS [MID], // [Project2].[MAID] AS [MAID], // [Project2].[mTitle] AS [mTitle], // [Project2].[mContent] AS [mContent] //FROM ( // SELECT [Limit1].[AID] AS [AID], // [Limit1].[aKey] AS [aKey], // [Limit1].[C1] AS [C1], // [Extent2].[MID] AS [MID], // [Extent2].[MAID] AS [MAID], // [Extent2].[mTitle] AS [mTitle], // [Extent2].[mContent] AS [mContent], // CASE // WHEN ([Extent2].[MID] IS NULL) THEN CAST(NULL AS INT) // ELSE 1 // END AS [C2] // FROM ( // SELECT TOP(1) // [Extent1].[AID] AS [AID], // [Extent1].[aKey] AS [aKey], // 1 AS [C1] // FROM [dbo].[TCBaseAccount] AS [Extent1] // WHERE 1 = [Extent1].[AID] // ) AS [Limit1] // LEFT OUTER JOIN [dbo].[TCBaseMessage] AS [Extent2] // ON [Limit1].[AID] = [Extent2].[MAID] // ) AS [Project2] //ORDER BY // [Project2].[AID] ASC, // [Project2].[C2] ASC #endregion //若要性能高,可以自己写sql。sql中的列定义要符合模型的属性名,所以用AS关键字 //单表,只查账号表 // string sqlAcc = @"SELECT TOP 1000 [AID] AS [AccountId],[aKey] AS [Key] // FROM [EFTest].[dbo].[TCBaseAccount] WITH(NOLOCK) "; // var accounts2 = context.Database.SqlQuery<AccountContract>(sqlAcc).ToList(); //ToList()指定立即查询 //两表联查,查账号及其归属的消息 // string sqlAccMsg = @"SELECT ta.AID AS [AccountId],ta.aKey AS [Key],tm.MID AS [MessageId],tm.mTitle AS [Titlle],tm.mContent AS [Content] // FROM TCBaseAccount ta WITH(NOLOCK) // INNER JOIN TCBaseMessage tm WITH(NOLOCK) // ON tm.MAID = ta.AID "; // var accounts3 = context.Database.SqlQuery<AccountContract>(sqlAccMsg).ToList(); //ToList()指定立即查询 //若量大,可能慢 //int effectRows = context.Database.ExecuteSqlCommand("delete from TCBaseMessage"); Console.Read(); } }