Select rows from table1 and all the children from table2 into an object
- by Patrick
I want to pull data from table "Province_Notifiers" and also fetch all corresponding items from table "Province_Notifier_Datas". The table "Province_Notifier" has a guid to identify it (PK), table "Province_Notifier_Datas" has a column called BelongsToProvinceID witch is a foreign key to the "Province_Notifier" tables guid.
I tried something like this:
var records = from data in ctx.Province_Notifiers
where DateTime.Now >= data.SendTime && data.Sent == false
join data2 in ctx.Province_Notifier_Datas on data.Province_ID equals data2.BelongsToProvince_ID
select new Province_Notifier
{
Email = data.Email,
Province_ID = data.Province_ID,
ProvinceName = data.ProvinceName,
Sent = data.Sent,
UserName = data.UserName,
User_ID = data.User_ID,
Province_Notifier_Datas = (new List<Province_Notifier_Data>().AddRange(data2))
};
This line is not working and i am trying to figure out how topull the data from table2 into that Province_Notifier_Datas variable.
Province_Notifier_Datas = (new List<Province_Notifier_Data>().AddRange(data2))
I can add a record easily by adding the second table row into the Province_Notifier_Datas but i can't fetch it back.
Province_Notifier dbNotifier = new Province_Notifier();
// set some values for dbNotifier
dbNotifier.Province_Notifier_Datas.Add(
new Province_Notifier_Data
{
BelongsToProvince_ID = userInput.Value.ProvinceId,
EventText = GenerateNotificationDetail(notifierDetail)
});
This works and inserts the data correctly into both tables.
Edit:
These error messages is thrown:
Cannot convert from 'Province_Notifier_Data' to 'System.Collections.Generic.IEnumerable'
If i look in Visual Studio, the variable "Province_Notifier_Datas" is of type System.Data.Linq.EntitySet
The best overloaded method match for 'System.Collections.Generic.List.AddRange(System.Collections.Generic.IEnumerable)' has some invalid arguments
Edit:
var records = from data in ctx.Province_Notifiers
where DateTime.Now >= data.SendTime && data.Sent == false
join data2 in ctx.Province_Notifier_Datas on data.Province_ID equals data2.BelongsToProvince_ID
into data2list
select new Province_Notifier
{
Email = data.Email,
Province_ID = data.Province_ID,
ProvinceName = data.ProvinceName,
Sent = data.Sent,
UserName = data.UserName,
User_ID = data.User_ID,
Province_Notifier_Datas = new EntitySet<Province_Notifier_Data>().AddRange(data2List)
};
Error 3 The name 'data2List' does not exist in the current context.
