Linq多表添加插入、更新操作、子查询

一、添加：

1、联合主键：主键全部相同，插入失败，至少要有一个不同。（主键唯一约束条件）

2、添加对象要另外命名创建

3、添加空数据时，得是空字符串 ：“ ”

二、更新：

1、先根据添加查询出数据，重新赋值要修改的字段，再更新（update）对象

 

三、（1）查询所有：

select * from User order by UserId

to Linq:

List<User> listUser= ctx.User.OrderBy(t=>t.UserId);

 

（2）查询某一列最大值：

SELECT NVL(MAX(VERSION), 0)    //如果version字段的查询结果是null ,则取0

to Linq：

ctx.Spcspechis.Where(t=>t.Factory==sFactory&&t.ChartId==sChartId).Select(t=>new {t.Version }).Max(t=>t.Version); //查询version字段的最大值

（3）多条件嵌套子查询：

 string  strQuery = "SELECT * FROM SPCSPECHIS WHERE FACTORY=? AND CHART_ID=? AND VERSION = (SELECT MAX(VERSION) FROM SPCSPECHIS  WHERE FACTORY=? AND CHART_ID=?  AND RELEASE_FLAG='Y' AND APPLY_START_TIME<=? AND(APPLY_END_TIME = ' ' OR APPLY_END_TIME >= ?) AND RELEASE_TIME <= ?)";

to Linq:

 spcspechis = ctx.Spcspechis.Where(t => t.Factory == sFactory && t.ChartId == sChartId && t.Version .CompareTo(ctx.Spcspechis.Where(a => a.Factory == sFactory && a.ChartId == sChartId && a.ReleaseFlag == "Y"
                   && a.ApplyStartTime.CompareTo(sApplyStartTime) <= 0 && ((a.ApplyEndTime == " ") || (a.ApplyEndTime.CompareTo(sApplyEndTime) >= 0
                   && a.ReleaseTime.CompareTo(sReleaseTime) <= 0))).Select(a => new { a.Version }).DefaultIfEmpty().Max()) == 0).FirstOrDefault();

=====

分两步，先查子查询(用DefaultIfEmpty，不然用FirstOrDefault会报异常Sequence contains no elements )：

decimal dversion =ctx.Spcspechis.Where(a => a.Factory == sFactory && a.ChartId == sChartId && a.ReleaseFlag == "Y"
                   && a.ApplyStartTime.CompareTo(sApplyStartTime) <= 0 && ((a.ApplyEndTime == " ") || (a.ApplyEndTime.CompareTo(sApplyEndTime) >= 0
                   && a.ReleaseTime.CompareTo(sReleaseTime) <= 0))).Select(a => new { a.Version }).DefaultIfEmpty().Max(t=>t.Version)) == 0

 spcspechis = ctx.Spcspechis.Where(t => t.Factory == sFactory && t.ChartId == sChartId && t.Version.CompareTo(dversion).FirstOrDefault();

注：子查询中要换个变量（用a）避免重复定义

（4）联表查询，结果只需多个列：

strQuery = "SELECT USER_ID, USER_DESC, SEC_GRP_ID, EMAIL_ID"
                                + " FROM SPCCHARTUSER"
                                + " WHERE FACTORY=?"
                                + " AND CHART_ID=?"
                                + " AND USER_ID>=?"
                                + " ORDER BY USER_ID ASC";

to Linq:

  var strQery1 = (new int[] { 1 }).Select(t=>new { UserId = "empty", UserDesc = "empty", SecGrpId = "empty", EmailId = "empty" }).ToList();


 strQery1 = ctx.Spcchartuser.Where(t => t.Factory == sFactory && t.ChartId == sChartId && t.UserId.CompareTo(sUserId) >= 0).OrderBy(t => t.UserId)
                        .Select(t=>new { t.UserId, t.UserDesc, t.SecGrpId, t.EmailId })
                        .ToList();

查询条件数组类型创建方式：

public SPCCoreType.SPC_Update_Chart_In_Tag_x_alarm_code_tbl[] x_alarm_code_tbl;

 

public struct SPC_Update_Chart_In_Tag_x_alarm_code_tbl
        {
            public string alarm_code;
        }

x_alarm_code_tbl =new SPCCoreType.SPC_Update_Chart_In_Tag_x_alarm_code_tbl[]
                {

                    new SPCCoreType.SPC_Update_Chart_In_Tag_x_alarm_code_tbl{ alarm_code ="1"},
                    new SPCCoreType.SPC_Update_Chart_In_Tag_x_alarm_code_tbl{ alarm_code ="1"},
                    new SPCCoreType.SPC_Update_Chart_In_Tag_x_alarm_code_tbl{ alarm_code ="1"},
                }

查询条件引用数组时：

POPCoreType.POP_Update_Image_In_Tag_item_list[] item_list = new POPCoreType.POP_Update_Image_In_Tag_item_list[]
             {
                 new POPCoreType.POP_Update_Image_In_Tag_item_list()
                 {
                     seq_num=1,
                     image_data="1"
                 },
                 new POPCoreType.POP_Update_Image_In_Tag_item_list()
                 {
                     seq_num=2,
                     image_data="1"
                 },
             };


             POPCoreType.POP_Update_Image_In_Tag Update_Image_In = new POPCoreType.POP_Update_Image_In_Tag()
             {
                 _cmn_in = cmn_in,
                 image_id = "1",
                 image_desc = "1",
                 item_list = item_list,
                 count = 2
             };

 

转载于:https://my.oschina.net/8824/blog/3067396