Hi,
Unfortunately, I am the only person in the company who hand much experience with Linq and so I have no-where else to turn when I get in a jam :S
I have a query where one of the columns must show a single row from a joined table when only one row exists, but must show a different value when there are multiple joined rows.
I originally had this column in a joined table so the query is separate to the main query but I will eventually need to integrate the two together.
Anyway - On with the code at hand:
private class WaitingReport
{
public int RunId { get; set; }
public int PackageId { get; set; }
public string DecoDescription { get; set; }
public string WaitingOnText { get; set; }
public string WaitingOnLink { get; set; }
}
private class RunComponent
{
public int RunId { get; set; }
public string ComponentName { get; set; }
}
private void PopulateWaitingTable()
{
WaitingCarmaTable.Reset();
CarmaContext db = new CarmaContext();
IQueryable<WaitingReport> iqWaitingReport =
RunProperty.QueryByNameAndNotValue(PropertyFacade.RUN_USER_INPUT_URL, "empty", db)
.Join(
db.Runs,
rp => rp.run_id,
r => r.run_id,
(rp, r) => new {r.run_id, rp.property_value, r.deco_id, r.package_id, r.action_id}
)
.Join(
db.Actions,
n => n.action_id,
a => a.action_id,
(n, a) => new WaitingReport
{
RunId = n.run_id,
PackageId = n.package_id.GetValueOrDefault(0),
DecoDescription = Deco.GetDecoDescription(n.deco_id,db),
WaitingOnText = a.action_name,
WaitingOnLink = n.property_value
}
);
IQueryable<RunComponent> iqComponent = iqWaitingReport
.Join(
db.Runs.Where(r => _showAll || r.contact.ToLower().Equals(WebInterfaceUtils.GetUserName().ToLower())),
rp => rp.RunId,
r => r.run_id,
(rp, r) => r)
.Join(
db.Deliveries,
r => r.package_id,
d => d.package_id,
(r, d) => new {r.run_id, d.component_id}
)
.Join(
db.Components,
n => n.component_id,
c => c.component_id,
(n, c) => new RunComponent { RunId = n.run_id, ComponentName = c.name }
)
.GroupBy(rc => rc.RunId)
.Select(rcg => new RunComponent
{
RunId = rcg.Key,
ComponentName = rcg.Count() > 1
? Segment.QueryByRunId(rcg.Key,db).segment_name
: rcg.Max().ComponentName
});
I have an extension method to convert these into DataTables but any kind of query activation triggers the error
Could not format node 'New' for execution as SQL
I have found that the final .
Select()
method is what throws the error but I cannot see why.
Any ideas?
I'll closely monitor this thread and I am willing to make any changes to narrow down the reason for the error
TIA ^_^