CODEWITHSUNDEEP
c#linq-to-sql
comidos
comidos

Reputation: 123

LinqToSql Equivalent to IN

How can do in LinqToSql an equivalent to the IN clause?

I have a SQL statement like this:

select *
from funktion 
where institution_id IN (select id from institution where kreis_id = 36)

How can I express that in a Linq-to-Sql statement?

For explanation: the table funktion is something like CAPACITY and the table institution is representing INSTITUTIONS with hierarchy structure.

Every function is referenced between the tables with institution.id == funktion.institution_id. The hierarchy structures are from high to low: VERBAND, BEZIRK, KREIS, VEREIN.

Every VERBAND consists of several BEZIRK. Each BEZIRK consist of several KREIS. Each KREIS consist of several VEREIN. Every Hierarchy-layer has its ID for lower layers in referenced ID columns. So the lowest layer has a beside its own ID (Primary Key) a column for KREIS_ID, BEZIRK_ID, VERBAND_ID to know to which next levels it belongs. So I have to make statements for each layer to see the functions of all lower levels.

In my SQL example shown earlier, I have a KREIS that needs to get all FUNCTIONS where the INSTITUTION_ID is in a select of IDS where the KREIS_ID is like the OWN_ID (here for example 36).

I hope someone could help me. I am very new in LINQ, ,but have to change old software structures to a newer DataSource.

Upvotes: 0

Views: 52

Answers (3)

Edwin ZAP
Edwin ZAP

Reputation: 463

The IN statement in linq is Contains(). You can do something like:

var institutions = db.Institutions.Where(i => i.kreisId == 36).Select(i => i.id);
var funktions = db.Funktion.Where(f => institutions.Contains(f.instition_id)).ToList();

Upvotes: 3

jdweng
jdweng

Reputation: 34433

Try following :

   class Program
    {

        static void Main(string[] args)
        {
            //select * from funktion where institution_id IN (select id from institution where kreis_id = 36)
            Context db = new Context();
            var results = db.funktion.Select(x => db.institution.Where(y => (x.institution_id == y.id) && (y.kreis_id == 36))).ToList();
 
        }
    }
    public class Context
    {
        public List<funktion> funktion { get; set; }
        public List<institution> institution { get; set; }
    }
    public class funktion
    {
        public string institution_id { get; set; }
    }
    public class institution
    {
        public string id { get; set; }
        public int kreis_id { get; set; }
    }

Upvotes: 0

comidos
comidos

Reputation: 123

Solved with a JOIN in combination with WHERE:

from funktion in db.funktion
where funktion.deletedFlag == 0 && (funktion.bis == null || funktion.bis > DateTime.Now)
                            
join institution in db.institution on funktion.institution_id equals institution.id
where institution.kreis_id == mySession.Current.benutzer.institution_id
                     
select new
{
  ...
});

Upvotes: 1

Related Questions