EF直接执行sql 存储过程

执行SQL：

using (var cfc = new cfContext())
{
    var accountlist = cfc.Account.SqlQuery("SELECT TOP 10 * FROM Account");
    foreach (var a in accountlist)
    {
        Console.WriteLine("{0}-{1}-{2}", a.ID, a.Name, a.AddTime);
    }
}

执行存储过程：

public class CompanyInfo
{
    public int CompanyId { get; set; }
    public string CompanyName { get; set; }
}

我们在SQLServer中定义一个存储过程如下：

sqlsqlCREATE PROCEDURE [dbo].[SelectCompanies]
@dateAdded as DateTime
AS
BEGIN
SELECT CompanyId, CompanyName
FROM Companies
WHERE DateAdded > @dateAdded
END

使用的时候，我们也可以传入对应的参数：

String sql = @"SelectCompanies {0}";
var companies = context.Database.SqlQuery<CompanyInfo>(
    sql,
    DateTime.Today.AddYears(-10));
foreach (var companyInfo in companies)
{
    // SqlQuery方法会帮我们拼接好sql
}

上面的存储过程是查找类型的存储过程，不会影响到数据库的记录，那么如果是修改类型的存储过程呢，比如下面的这个：

sqlsqlCREATE PROCEDURE dbo.UpdateCompanies
@dateAdded as DateTime,
@activeFlag as Bit
AS
BEGIN
UPDATE Companies
Set DateAdded = @dateAdded,
IsActive = @activeFlag
END

那么我们就需要用到的另外一个方法是ExecuteSqlCommand：

var sql = @"UpdateCompanies {0}, {1}";
var rowsAffected = context.Database.ExecuteSqlCommand(
    sql, DateTime.Now, true);
// 他会返回受影响的记录数的

EF中异步的使用：

private static async Task<IEnumerable<Company>> GetCompaniesAsync()
{
    using (var context = new Context())
    {
        return await context.Companies
            .OrderBy(c => c.CompanyName)
            .ToListAsync();
    }
}

上面的ToListAsync会异步返回数据。

还有下面的异步保存：

private static async Task<Company> AddCompanyAsync(Company company)
{
    using (var context = new Context())
    {
        context.Companies.Add(company);
        await context.SaveChangesAsync();
        return company;
    }
}

异步查找：

private static async Task<Company> FindCompanyAsync(int companyId)
{
    using (var context = new Context())
    {
        return await context.Companies.FindAsync(companyId);
    }
}

异步统计：

private static async Task<int> ComputeCountAsync()
{
    using (var context = new Context())
    {
        return await context.Companies.CountAsync(c => c.IsActive);
    }
}

异步循环遍历：

private static async Task LoopAsync()
{
    using (var context = new Context())
    {
        await context.Companies.ForEachAsync(c =>
        {
            c.IsActive = true;
        });
        await context.SaveChangesAsync();
    }
}

并发性问题：

private static void ConcurrencyExample()
{
    var person = new Person
    {
        BirthDate = new DateTime(1970, 1, 2),
        FirstName = "Aaron",
        HeightInFeet = 6M,
        IsActive = true,
        LastName = "Smith"
    };
    int personId;
    using (var context = new Context())
    {
        context.People.Add(person);
        context.SaveChanges();
        personId = person.PersonId;
    }
    // simulate second user
    using (var context = new Context())
    {
        context.People.Find(personId).IsActive = false;
        context.SaveChanges();
    }
    // back to first user
    try
    {
        using (var context = new Context())
        {
            context.Entry(person).State = EntityState.Unchanged;
            person.IsActive = false;
            context.SaveChanges();
        }
        Console.WriteLine("Concurrency error should occur!");
    }
    catch (DbUpdateConcurrencyException)
    {
        Console.WriteLine("Expected concurrency error");
    }
    Console.ReadKey();
}