CODEWITHSUNDEEP
c#linq-to-entities
EgyEast
EgyEast

Reputation: 1572

How to use Linq to Entity query to search for many to many relationships

I've a Sql Server database with 3 tables :

1st table named "Drugs" Has fields : "DrugID", "Name"

2nd table named "Ingredients: has fields "IngredientID", "Name"

3rd table represents many to many relationship between the other 2 tables and has fields "DrugID", "IngredientID", "Concentration"

Suppose I have a drug in "Drugs" table named "DrugA" Which has many to many relationship to 3 Ingredients in table "Ingredients"

What is the best linq to entities query to search for all other drugs that has relationship to ((all)) the same ingredients

Upvotes: 1

Views: 57

Answers (1)

Gaurang Dave
Gaurang Dave

Reputation: 4046

This was not easy. I simulated your DB scenario by creating relative Classes and Lists. I ran and tested this code.

Following implementation will return all DrugId where there are common Ingredients as Drug with Name "Drug1".

CLASSES

public class Drug
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Ing
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Relation
{
    public int DId { get; set; }
    public int IId { get; set; }
}

LINQ IMPLEMENTATION

List<Drug> drugs = new List<Drug>();
List<Ing> ings = new List<Ing>();
List<Relation> rel = new List<Relation>();

for (int i = 1; i <= 3; i++)
{
    drugs.Add(new Drug() { Id = i, Name = "Drug" + i });
    ings.Add(new Ing() { Id = i, Name = "Ing" + i });
}

rel.Add(new Relation() { DId = 1, IId = 1 });
rel.Add(new Relation() { DId = 1, IId = 2 });
rel.Add(new Relation() { DId = 1, IId = 3 });
rel.Add(new Relation() { DId = 2, IId = 1 });
rel.Add(new Relation() { DId = 2, IId = 2 });
rel.Add(new Relation() { DId = 3, IId = 2 });
rel.Add(new Relation() { DId = 3, IId = 3 });
rel.Add(new Relation() { DId = 3, IId = 1 });


var ingredients = rel
        .Join(drugs, r => r.DId, d => d.Id, (r, d) => new { r, d })
        .Where(rdi => rdi.d.Name == "Drug1")
        .Select(m => m.r.IId).OrderBy(m => m).ToList();

var drugIds = rel.GroupBy(r => r.DId, (key, g) => new { D = key, I = g.Select(a => a.IId).OrderBy(a => a).ToList() })
        .Where(di => Enumerable.SequenceEqual(di.I, ingredients))
        .Select(s => s.D).ToList();

Upvotes: 1

Related Questions