How to query complex n:m relationship for getting an IEnumerable in C#

Question

In my sql database I have a many to many (n:m) relationship as given below:

For my asp.net web application (with EF and database first approach) I need to extract all records (with C# and linq) of the table Quality for all groups that are members in the same associations that a given group is member of.

Below I have given sample data to demonstrate the needed query result for a given group with G_Id = 1

From the sample data we see that the given group is a member of association A_Id = 1 and A_Id = 2. So we need to look which other groups are members of these associations. In the given data we see, that the group with G_Id = 2 is also a member of association A_Id = 1 and the group with G_Id = 3 is also a member of A_Id = 2. Thus we have to collect all records from the table Quality where G_Id = 1, G_Id = 2 and G_Id = 3 and the query result is:

I was able to write a working C# IEnumerable controller action (see below) for getting all Quality records for one group but failed to come up with a query that also accounts for the records of the associated groups.

public IEnumerable<Quality> Get()
{
    int g_Id = myAppPrincipal.G_Id;
    var group = UnitOfWork.GetById<Group>(g_Id);
    var groupQualities = group.Qualities;
    // Qualities is a public virtual ICollection of Quality inside the class Group

    IList<Quality> result = groupQualities.Select(entity => new Quality()
    {
        Q_Id = entity.Q_Id,
        Description = entity.Description,
        ...
    }).ToList();

    return result;
}

If someone could help me with building the correct query I'd be grateful.

See below class structure:

public class Entity
{
    /// <summary>
    /// Unique identifier
    /// </summary>
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual int Id
    {
        get;
        set;
    }

    /// <summary>
    /// Concurrency check property
    /// </summary>
    [Timestamp]
    public virtual byte[] Version
    {
        get;
        set;
    }

    /// <summary>
    /// Determines whether entity is new
    /// </summary>
    [Bindable(false)]
    [ScaffoldColumn(false)]
    public virtual bool IsNew
    {
        get
        {
            return Id == 0;
        }
    }

    public override string ToString()
    {
        return this.DumpToString();
    }
}

public class Association: Entity
{
    public string Description { get; set; }
}

public class Assoc_Group: Entity
{
    public int A_ID { get; set; }
    public int G_ID { get; set; }

    [IgnoreDataMember]
    public override byte[] Version
    {
        get;
        set;
    }
}

public class Group: Entity
{
    public Group()
    {
        Qualities = new List<Quality>();
    }

    public string Description { get; set; }

    public virtual ICollection<Quality> Qualities { get; set; }

}

public class Quality: Entity
{
    public int? G_ID { get; set; }
    public string Description { get; set; }

    public virtual Group Group { get; set; }

    [IgnoreDataMember]
    public override byte[] Version
    {
        get;
        set;
    }
}

and corresponding mappings:

public class AssociationMap : EntityTypeConfiguration<Association>
{
    public AssociationMap()
    {
        // Primary Key
        HasKey(t => t.Id);

        // Properties
        Property(t => t.Description)
            .IsRequired();

        // Table & Column Mappings
        ToTable("Association");
        Property(t => t.Id).HasColumnName("A_ID");
        Property(t => t.Description).HasColumnName("Description");

        Ignore(t => t.Version);
    }
}

class Assoc_GroupMap : EntityTypeConfiguration<Assoc_Group>
{
    public Assoc_GroupMap()
    {

        ToTable("Assoc_Group");
        Property(t => t.Id).HasColumnName("AG_ID");
        Property(t => t.A_ID).HasColumnName("A_ID");
        Property(t => t.G_ID).HasColumnName("G_ID");

        Ignore(property => property.Version);
    }
}

public class GroupMap : EntityTypeConfiguration<Group>
{
    public GroupMap()
    {
        // Primary Key
        HasKey(t => t.Id);

        // Table & Column Mappings
        ToTable("Group");
        Property(t => t.Id).HasColumnName("G_ID");


        Ignore(t => t.Version);
    }
}


public class QualityMap : EntityTypeConfiguration<Quality>
{
    public QualityMap()
    {
        // Primary Key
        HasKey(t => t.Id);

        // Table & Column Mappings
        ToTable("Quality");
        Property(t => t.Id).HasColumnName("Q_ID");
        Property(t => t.G_ID).HasColumnName("G_ID");
        ...

        // Relationships
        HasOptional(t => t.Group)
            .WithMany(t => t.Qualities)
            .HasForeignKey(d => d.G_ID);
    }
}

Answer 1

As far I understand, you need all the group qualities that fall in all the parent associations of some specific group, so:

public IEnumerable<Quality> Get()
{
    int g_Id = myAppPrincipal.G_Id;
    var group = UnitOfWork.GetById<Group>(g_Id);
    var associatedGroups = group.Assoc_Group.Groups;
    var qualities = new List<Quality>();
    foreach (var assoc in associatedGroups.Select(t => t.Association).Distinct())
    {
        qualities.AddRange(assoc.Assoc_Groups.SelectMany(t => t.group.Qualities).ToList());
    }

    var result = qualities.Distinct();
    return result;
}

In the above case, I am expecting GetById getting the associated entities also, if no, then you have to change to GetById method to include associated entities. Otherwise, you can get the associated separately by making separate calls.

Answer 2

You can use nested queries like this:

DECLARE @GivenGroup AS Int = 1

SELECT DISTINCT Q_Id
FROM Quality q
WHERE q.G_Id IN
(
    SELECT DISTINCT G_Id
    FROM Assoc_Group ag
    WHERE ag.A_Id IN
    (
        SELECT a.A_Id
        FROM Association a
        INNER JOIN Assoc_Group ag ON a.A_Id = ag.A_Id
        WHERE ag.G_Id = @GivenGroup
    )
)

At least for SQL Server.

You can try it here: http://sqlfiddle.com/#!18/6dcc6/2

To query your SQL Database with C# you can use the SqlClient

var SQL = new StringBuilder();
// Build SQL here. use SQL.AppendLine("SELECT ...")
var cn = new 
System.Data.SqlClient.SqlConnection(your_sql_connection_string);
var da = new System.Data.SqlClient.SqlDataAdapter(SQL.ToString(), cn);
var datatbl = new DataTable();
da.SelectCommand.Parameters.Add("@GivenGroup", SqlDbType.Int).Value = 1; // use parameterization always.
try {
    da.SelectCommand.Connection.Open();
    da.Fill(datatbl);
}
catch (Exception ex)
{
    // error handling here
}
finally
{
    cn.Close();
    cn.Dispose();
    da.Dispose();
}
// read from datatable
foreach (DataRow row in datatbl.Rows)
{
    Console.WriteLine(row["G_Id"].ToString());
}

May contain errors.