Converting a given SQL Query to LINQ-CodePudding

I'm new to creating a LINQ so I'm having a hard time converting this SQL query into LINQ. Can someone help me please

SELECT *
FROM myTable1
WHERE (Flag1 <> 'X' OR Flag2 != 'X' OR Flag3 != 'X')
  AND number IN (SELECT externalid FROM db2.myTable2 WHERE item = 6)

This is what I've already tried

//get external id
            var externalNumber = from s in db2.myTable2
                             where s.item == 6
                             select externalid;

            var query = from f in db1.myTable1
                        where (f.Flag1 != "X" || f.Flag2 != "X" || f.Flag3 != "X") && f.number == externalNumber
                        select f;

CodePudding user response：

Direct translation is:

var query = 
    from f in db.myTable1
    where (f.Flag1 != "X" || f.Flag2 != "X" || f.Flag3 !="X") &&
        db.myTable2.Where(s => s.item == 6).Select(s => s.externalId).Contains(f.number)
    select f;

IN in LINQ has analogue Contains

CodePudding user response：

var externalid = db2.myTable2Repository.FirstOrDefault(f=>f.item == 2).Result.externalid;

This first code return externalid from db2.myTable2

var data = myTable1Repository.GetAll().Where(w=>(w.Flag1 != X || w.Flag2 != X || w.Flag3 != X) && w.number == externalid)

Second code: GetAll() is a method of your repository (or baseRepository) and Where() is part of linq

First part of where -> (w.Flag1 != X || w.Flag2 != X || w.Flag3 != X) is self explain

Second part -> (&& w.number == externalid): I'm not very good with sql so this second part may have got it wrong ,but if I understand correctly, you check the "number" property against a value from another table, so you can just look up this value and store it in a variable.

I'm sorry if I misunderstood the second part in the "and number in", I'm really not good at SQL, and for my bad English too.

If you have any questions we are here :D

CodePudding user response：

Try the Below code:

DatabaseEntities dc=new DatabaseEntities();
var res=dc.myTable1.Where(s=>s.Flag <> 'X' || Flag2 !='X' || Flag3 !='X' && s.number.Contains(dc.myTable2.Where(m=>m.item==6)))