using Microsoft.EntityFrameworkCore;
using System.Data.Common;

public class CDataDbContext : DbContext
{
    // 实体集（以 Salesforce Account 表为例）
    public DbSet<SalesforceAccount> Accounts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string connectionString = 
            "[email protected];Password=password;Security Token=your_token;";
        
        // 使用 CData Salesforce Provider
        optionsBuilder.UseSqlServer(
            new CData.Salesforce.SalesforceConnection(connectionString),
            options => options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
        );
    }
}

// 实体类映射 Salesforce Account 对象
[Table("Account")]
public class SalesforceAccount
{
    [Key]
    public string Id { get; set; }
    public string Name { get; set; }
    public decimal AnnualRevenue { get; set; }
}

using var context = new CDataDbContext();

// 查询年收入大于 100 万的客户
var highValueAccounts = await context.Accounts
    .Where(a => a.AnnualRevenue > 1_000_000)
    .OrderBy(a => a.Name)
    .ToListAsync();

var newAccount = new SalesforceAccount 
{
    Name = "Future Tech Corp",
    AnnualRevenue = 5_000_000
};

context.Accounts.Add(newAccount);
await context.SaveChangesAsync(); // 同步到 Salesforce

// 配置 Excel DbContext
public class ExcelDbContext : DbContext
{
    public DbSet<ExcelProduct> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string excelConnStr = "Excel File='C:/Data/Products.xlsx';";
        optionsBuilder.UseSqlServer(new CData.Excel.ExcelConnection(excelConnStr));
    }
}

// 跨数据源联合查询
var combinedData = 
    from account in contextSalesforce.Accounts
    join product in contextExcel.Products 
        on account.Industry equals product.Category
    select new { account.Name, product.Price };

var result = context.Database
    .SqlQueryRaw<MongoResult>("EXEC FindDocuments @Collection, @Query", 
        new SqlParameter("@Collection", "orders"),
        new SqlParameter("@Query", "{ 'status': 'pending' }"))
    .ToList();

// 批量插入（需 CData 驱动支持 BatchSize 配置）
context.ChangeTracker.AutoDetectChangesEnabled = false;
for (int i = 0; i < 1000; i++)
{
    context.Products.Add(new ExcelProduct { ... });
    if (i % 100 == 0)
    {
        await context.SaveChangesAsync();
        context.ChangeTracker.Clear();
    }
}

// 启用本地缓存（减少 API 调用）
var connectionString = "User=...;Cache Location=/temp;Cache Tolerance=300;";

optionsBuilder.UseLoggerFactory(LoggerFactory.Create(builder => 
    builder.AddConsole()))
.EnableSensitiveDataLogging();

// 使用 MiniProfiler
services.AddDbContext<CDataDbContext>(options => 
    options.UseSqlServer(connection)
    .AddMiniProfiler());