.NET Core 数据库查询语句

微软文档

https://docs.microsoft.com/zh-cn/previous-versions/dotnet/netframework-4.0/ee358758%28v%3dvs.100%29

一.LINQ语法查询

1. 结果不要属性名以数组形式返回

var role = from e in _masterDB.EmployeeRoleAdminAcl
           join a in _masterDB.AdminAccessControlList on e.AdminAccessId equals a.AccessId
           where e.CompanyId == employee.CompanyId
           && employee.RoleIds.Contains(e.RoleId.ToString())
           && a.GroupName == "MobileReport"
           select a.AccessControl;


// 结果
[
    "EmployeeDelete",
    "AdvertisementView",
    "AdvertisementAdd",
    "AdvertisementEdit",
    "AdvertisementDelete",
    "NormalOperatingHourView",
]

二. 实体类查询

1.返回单列列表

var locs = context.Location.AsNoTracking()
.Where(l => cmps.Contains(l.CompanyId))
.Select(l => l.LocationId).Tolist();


//结果
List<string> 类型
location1
location2
...

2.返回带名字的Group by 列表

var result = _transactionContext.H_SalesMaster.AsNoTracking()
                            .Where(s => locs.Contains(s.LocationId)
                            && s.SalesDateTime.Year >= startYear
                            && s.SalesDateTime.Year <= endYear)
                            .GroupBy(s => new { s.SalesDateTime.Year })
                            .Select(s => new { Year = s.Key.Year, total = s.Sum(t => t.TotalAmount) });
foreach (var g in result)
{
    dict.Add(g.Year.ToString(), g.total);
    list.Add(dict);
}

//结果
g.Year  g.total

三.原生SQL语句使用

//插入
int rowsAffected = context.ExecuteStoreCommand("insert Department values ({0}, {1}, {2}, {3}, {4})", DepartmentID, "Engineering", 350000.0R, "2009-09-01", 2)
        Console.WriteLine("Number of affected rows: {0}", rowsAffected)

//查询
DepartmentInfo department  = context.ExecuteStoreQuery(Of DepartmentInfo) 
                      ("select * from Department where DepartmentID= {0}",
                       DepartmentID).FirstOrDefault();


//删除
rowsAffected = context.ExecuteStoreCommand("delete from Department where DepartmentID = {0}", DepartmentID);