.NET Core 数据库查询语句

桃扇骨 2023-02-16 04:04 48阅读 0赞

### 微软文档 ###

### [https://docs.microsoft.com/zh-cn/previous-versions/dotnet/netframework-4.0/ee358758%28v%3dvs.100%29][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 列表**

### 三.原生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);

[https_docs.microsoft.com_zh-cn_previous-versions_dotnet_netframework-4.0_ee358758_28v_3dvs.100_29]: https://docs.microsoft.com/zh-cn/previous-versions/dotnet/netframework-4.0/ee358758%28v%3Dvs.100%29