locked
Please help me with LINQ statement to give IN clause in where condition RRS feed

  • Question

  • User-1355965324 posted

    I have the following model

    DepotModel
    DepotNo int
    Depotname string

    DepotBreakLock DepotNo int [Column("isLocked")] public bool isBreakLocked { get; set; }
    [Not Mapped]
    DepotName string UserDepotLink UserId int DepotNo int

    I want to get the  same sql given below  in LINQ  please help . The result  should be getting into the  Var 'Datalist'

    var Datalist= 
    select A.DepotNo,A.DepotName,b.isLocked, CASE WHEN B.isLocked = 1 THEN 'Closed' ELSE 'Open' END AS Status from Depot A left join BreakLogLock] B on A.DepotNo=B.DepotNo where A.DepotNo in (select DepotNo from UserDepotLink where userid = 3)

    Thursday, November 21, 2019 10:13 AM

Answers

  • User711641945 posted

    Hi polachan,

    You could change like below to use left join in linq:

    var deptno = (from u in _context.UserDepotLink
                    where u.UserId == 3
                    select u.DepotNo).ToList();
    var Datalist = (from a in _context.DepotModel
        join b in _context.DepotBreakLock
        on a.DepotNo equals b.DepotNo 
    into temp from b in temp.DefaultIfEmpty() where deptno.Contains(a.DepotNo) select new { Depotno = a.DepotNo, DepotName = a.Depotname, IsLocked = b==null?b.isBreakLocked==false:b.isBreakLocked, Status = (b == null ? "Open" : "Close") }).ToList();

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 22, 2019 9:31 AM

All replies

  • User711641945 posted

    Hi polachan,

    Here is a simple demo like below:

    1.Model:

    public class DepotModel
    {
        [Key]
        public int DepotNo { get; set; }
        public string Depotname { get; set; }
    }
    public class DepotBreakLock
    {
        [Key]
        public int Id { get; set; }
        public int DepotNo { get; set; }
        [Column("isLocked")]
        public bool isBreakLocked { get; set; }
        [NotMapped]
        public string DepotName { get; set; }
    }
    public  class UserDepotLink
    {
        [Key]
        public int UserId { get; set; }
        public int DepotNo { get; set; }
    }

    2.linq:

    var deptno = (from u in _context.UserDepotLink
                    where u.UserId == 3
                    select u.DepotNo).ToList();
    var Datalist = (from a in _context.DepotModel
                    join b in _context.DepotBreakLock
                    on a.DepotNo equals b.DepotNo
                    where deptno.Contains(a.DepotNo)
                    select new
                    {
                        Depotno = a.DepotNo,
                        DepotName = a.Depotname,
                        IsLocked = b.isBreakLocked,
                        Status = (b.isBreakLocked == true ? "Closed" : "Open")
                    }).ToList();

    Best Regards,

    Rena

    Friday, November 22, 2019 3:13 AM
  • User-1355965324 posted

    Many Thanks Rena for the help ,

    I assume in your Linq , there is no left join with the table  depotmodel and DepotBreaklock. I want to display all the depots from depot table  even if  it doesnot exist in the table Breaklock.  for example

    Depot Table
    A
    B
    C
    D
    E
    F
    G
    
    User DepotLink
    Userid       Depot
    3             A
    3             E
    3             F
    3             D
    3             G
    4             A
    4             C
    
    BreakLock
    Depot         IsLocked
    A               0
    B               1
    C               0
    D               1
    E               1
    
    SQL result for user id 3 should be
    Depot        IsLocked       Userid
    A              Open          3
    E              Close         3
    F              Open          3 // If no record exist in the locktable that would be open
    D              Close         3
    G              Open          3
    



    Friday, November 22, 2019 6:53 AM
  • User711641945 posted

    Hi polachan,

    You could change like below to use left join in linq:

    var deptno = (from u in _context.UserDepotLink
                    where u.UserId == 3
                    select u.DepotNo).ToList();
    var Datalist = (from a in _context.DepotModel
        join b in _context.DepotBreakLock
        on a.DepotNo equals b.DepotNo 
    into temp from b in temp.DefaultIfEmpty() where deptno.Contains(a.DepotNo) select new { Depotno = a.DepotNo, DepotName = a.Depotname, IsLocked = b==null?b.isBreakLocked==false:b.isBreakLocked, Status = (b == null ? "Open" : "Close") }).ToList();

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 22, 2019 9:31 AM
  • User-1355965324 posted

    Many Thanks .

    I just changed your LINQ as given below

     

     var data = (from d in goContext.goDepot
                            join link in goContext.goUserDepotLink on d.DepotNo equals link.DepotNo
                            join l in goContext.goBreakDownLogLock on d.DepotNo equals l.DepotNo into cuc
                            from x in cuc.DefaultIfEmpty()
                            where link.UserID == userId
                            select new BreakDownLogLock
                            {
                                DepotNo = d.DepotNo,
                                DepotName = d.DepotName,
                                Status = (x.isBreakLocked==true? "Closed": "Open")
    
                            } ).ToList();
    
               
                return data;

    Friday, November 22, 2019 9:46 AM