LINQ: Fill objects from left join

Question

I'm new to linq. How do I load my objects using LINQ from a left join database query (PostGIS).

This is my database query:

SELECT          
                dt.id, 
                dt.type, 
                dta.id as "AttId",
                dta.label,
                dtav.id as "AttValueId",
                dtav.value

FROM            public."dataTypes" dt, 
                public."dataTypeAttributes" dta
LEFT JOIN       public."dataTypeAttributeValues" dtav
ON              dta.id = "dataTypeAttributeId"        
WHERE           dt.id = dta."dataTypeId"
ORDER BY        dt.type, dta.label, dtav.value

And here is example output:

I have 3 entities:

public class TypeData
{
    public int ID { get; set; }
    public string Type { get; set; }
    public TypeDataAttribute[] Attributes { get; set; }

}
public class TypeDataAttribute
{
    public int ID { get; set; }
    public string Label { get; set; }
    public TypeDataAttributeValue[] Values { get; set; }
}

public class TypeDataAttributeValue
{
    public int ID { get; set; }
    public string Value { get; set; }
}

Update

Here is where I get stuck:

...
using (NpgsqlDataReader reader = command.ExecuteReader())
{

    if (reader.HasRows)
    {
        IEnumerable<TypeData> typeData = reader.Cast<System.Data.Common.DbDataRecord>()
            .GroupJoin( //<--stuck here.
    }
...

SOLUTION:

using AmyB's answer, this is what filled my objects:

using (NpgsqlDataReader reader = command.ExecuteReader())
{

    if (reader.HasRows)
    {

        var groups = reader.Cast<System.Data.Common.DbDataRecord>()
            .GroupBy(dr => new { ID = (int)dr["id"], AttID = (int)dr["AttId"] })
            .GroupBy(g => g.Key.ID);

        typeDataList = (
            from typeGroup in groups
            let typeRow = typeGroup.First().First()
            select new TypeData()
            {
                ID = (int) typeRow["id"],
                Type = (string) typeRow["type"],
                Attributes = 
                (
                    from attGroup in typeGroup
                    let attRow = attGroup.First()
                    select new TypeDataAttribute()
                    {
                        ID = (int)attRow["AttId"],
                        Label = (string)attRow["label"],
                        PossibleValues =
                        (
                            from row in attGroup
                            where !DBNull.Value.Equals(attRow["AttValueId"])
                            select new TypeDataAttributeValue() { ID = (int)row["AttValueId"], Value = (string)row["value"] }
                        ).ToArray()
                    }
                ).ToArray()
            }
        );
    }
}

Answer 1

So - if I understand right - you have a database query that you are happy with, but you want to take the row-column shaped result and project it into a hierarchically shaped result.

---

Suppose the results are in a List<Row>

public class Row
{
  public int id {get;set;}
  public string type {get;set;}
  public int attid {get;set;}
  public string label {get;set;}
  public int? attvalueid {get;set;}
  public string value {get;set;}
}

Then you would group twice, and turn each top-level group into a Type, each child-level group into an Attribute and each row into a Value (if the row is not an empty value).

List<Row> queryResult = GetQueryResult();

//make our hierarchies.
var groups = queryResult
  .GroupBy(row => new {row.id, row.attid})
  .GroupBy(g => g.Key.id);

//now shape each level
List<Type> answer =
(
  from typeGroup in groups
  let typeRow = typeGroup.First().First()
  select new Type()
  {
    id = typeRow.id,
    type = typeRow.type,
    Attributes =
    (
      from attGroup in typeGroup
      let attRow = attGroup.First()
      select new Attribute()
      {
        id = attRow.attid,
        label = attRow.label
        Values =
        (
          from row in attRow
          where row.attvalueid.HasValue  //if no values, then we get an empty array
          select new Value() {id = row.attvalueid, value = row.value }
        ).ToArray()
      }
    ).ToArray()
  }
).ToList();

Answer 2

Try GroupJoin sytnax

There is also Join syntax for regular inner join

This link has an example of group join

Answer 3

 var q = (from dt in public."dataTypes"
         // Join the second table to the first, using the IDs of the two tables <-- You may join on different columns from the two tables
         join dta in public."dataTypeAttributes" on
         new { ID = dt.id } equals
         new { ID = dta.id }
         // Join the third table to the first, using the IDs of the two tables
         join dtav in public."dataTypeAttributeId" on
         new { ID = dt.id } equals
         new { ID = dtav.id }
         // Conditional statement
         where dt.id == dta."dataTypeId"
         // Order the results
         orderby dt.type, dta.label, dtav.value
         // Select the values into a new object (datObj is a created class with the below variables)
         select new datObj() {
            ID = dt.id,
            Type = dt.type,
            AttID = dta.id,
            Label = dta.label,
            AttValueID = dtav.id,
            AttValue = dtav.value
         }).ToList()

This will return a List that match your where statement, in the order specified by the orderby statement.

Not exactly what you need, but should give you an example of what you should be doing.

Answer 4

Yopu should check this: http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/