Converting a given SQL Query to LINQ

Question

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;

What have YOU tried so far? Where are you getting stuck? We'll help with your efforts - but we won't just write the whole code for you — marc_s
– marc_s, Commented Dec 14, 2021 at 6:00
hi @marc_s, I've updated the statement above of what I've already done — sswswsw
– sswswsw, Commented Dec 14, 2021 at 7:18
A good start would be to find an O/R mapper that supports LINQ. — Gert Arnold
– Gert Arnold, Commented Dec 14, 2021 at 7:54

Svyatoslav Danyliv · Accepted Answer · 2021-12-14 09:04:00Z

1

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

answered Dec 14, 2021 at 9:04

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sswswsw Over a year ago

I'm getting this error "The specified LINQ expression contains references to queries that are associated with different contexts."

Svyatoslav Danyliv Over a year ago

You cannot combine queries from two different contexts.

marc_s · Accepted Answer · 2021-12-14 07:28:36Z

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

Sathiya · Accepted Answer · 2021-12-14 09:10:14Z

0

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)))

answered Dec 14, 2021 at 9:10

Sathiya

2392 silver badges2 bronze badges

Collectives™ on Stack Overflow

Converting a given SQL Query to LINQ

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related