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

本文深入探讨了SQL与Linq在数据操作上的应用,包括添加、更新数据的方法,以及如何使用Linq进行复杂查询,如联合主键、条件筛选、子查询、联表查询等,为开发者提供了实用的编程技巧。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、添加:

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值