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()
}
);
}
}
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…