Good morning,
I have following SQLite query which I need to transfer to LINQ:
"SELECT hostname, macaddress, uuid, group_concat(collection.collection_name) AS collection, daterequested, daterealized, requester, action_name AS action, done_name as done, comment FROM computerimport
LEFT OUTER JOIN action ON action.action_id = computerimport.action_id
LEFT OUTER JOIN done ON done.done_id = computerimport.done_id
LEFT OUTER JOIN computercollection ON computerimport.computer_id = computercollection.computer_id
LEFT OUTER JOIN collection ON computercollection.collection_id = collection.collection_id
GROUP BY computerimport.computer_id
ORDER BY daterequested;"
As you can see I have some LEFT OUTER JOINs and also using a group_concat to put everything from one table in just one field so that when a device exists in the computercollection twice I only get one record. My LINQ query looks like this;
await (from ci in _context.ComputerImports
join re in _context.Results on ci.result_id equals re.result_id
join ac in _context.Actions on ci.action_id equals ac.action_id
join cc in _context.ComputerCollections on ci.computer_id equals cc.computer_id into gjcc
from subcc in gjcc.DefaultIfEmpty()
join co in _context.Collections on subcc.collection_id equals co.collection_id into gjco
from subco in gjco.DefaultIfEmpty()
select new OverviewViewModel
{
computer_id = ci.computer_id,
hostname = ci.hostname,
uuid = ci.uuid,
collection_id = subcc.collection_id,
collection_name = subco.collection_displayname,
daterequested = ci.daterequested,
daterealized = ci.daterealized,
action_id = ci.action_id,
action_name = ac.action_name,
result_id = ci.result_id,
result_name = re.result_name,
comment = ci.comment
}).OrderByDescending(e => e.daterequested).ToListAsync();
But I also tried a group by with a String join:
await (from t in (from ci in _context.ComputerImports
join re in _context.Results on ci.result_id equals re.result_id
join ac in _context.Actions on ci.action_id equals ac.action_id
join cc in _context.ComputerCollections on ci.computer_id equals cc.computer_id into gjcc
from subcc in gjcc.DefaultIfEmpty()
join co in _context.Collections on subcc.collection_id equals co.collection_id into gjco
from subco in gjco.DefaultIfEmpty()
select new {ci.computer_id, ci.hostname, ci.uuid, subcc.collection_id, subco.collection_displayname, ci.daterequested, ci.daterealized, ci.action_id, ac.action_name, ci.result_id, re.result_name, ci.comment})
group t by new { t.computer_id, t.hostname, t.uuid, t.daterequested, t.daterealized, t.action_id, t.action_name, t.result_id, t.result_name, t.comment } into dg
select new OverviewViewModel
{
computer_id = dg.Key.computer_id,
hostname = dg.Key.hostname,
uuid = dg.Key.uuid,
collection_id = dg.Key.result_id,
collection_name = String.Join(",", dg.Select(d => d.collection_displayname).Distinct()),
daterequested = dg.Key.daterequested,
daterealized = dg.Key.daterealized,
action_id = dg.Key.action_id,
action_name = dg.Key.action_name,
result_id = dg.Key.result_id,
result_name = dg.Key.result_name,
comment = dg.Key.comment
}).OrderByDescending(e => e.daterequested).ToListAsync();
So far no luck. Is there a way to make this query easier? Right now, compared to the SQL language it does not feel very easy and readable.
Update: In Powershell with MSSQL it seems also quite easy:
$query = "SELECT hostname, uuid, STRING_AGG (TRIM(collection.collection_displayname), ' - ') as collection, daterequested, daterealized, requester, action_name AS action, result_name as result, comment
FROM computerimport
LEFT OUTER JOIN action ON action.action_id = computerimport.action_id
LEFT OUTER JOIN result ON result.result_id = computerimport.result_id
LEFT OUTER JOIN computercollection ON computerimport.computer_id = computercollection.computer_id
LEFT OUTER JOIN collection ON computercollection.collection_id = collection.collection_id
GROUP BY computerimport.computer_id, computerimport.hostname, computerimport.uuid, computerimport.daterequested, computerimport.requester, computerimport.daterealized, action_name, result_name, computerimport.comment
ORDER BY daterequested"
Update:
I took my working query and tried to create a new one out of it:
var result = ovm.GroupBy(cc => new {cc.computer_id, cc.hostname, cc.uuid, cc.daterequested, cc.daterealized, cc.action_id, cc.action_name, cc.result_id, cc.result_name, cc.comment})
.Select(dd => new { omputer_id = dd.Key.computer_id,
hostname = dd.Key.hostname,
uuid = dd.Key.uuid,
collection_name = String.Join(" - ", dd.Select(d => d.collection_name).Distinct()),
daterequested = dd.Key.daterequested,
daterealized = dd.Key.daterealized,
action_id = dd.Key.action_id,
action_name = dd.Key.action_name,
result_id = dd.Key.result_id,
result_name = dd.Key.result_name,
comment = dd.Key.comment});
Unfortunately result saying: Select(d => d.collection_name)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."
Update: As recommended I am trying to use a SQL statement like suggested here: How to execute SqlQuery with Entity Framework Core 2.1?
So I created a new class with the field I need, added it to the model builder:
public DbSet<OverviewQuery> OverviewQueries { get; set; }
modelBuilder.Entity<OverviewQuery>().ToView(nameof(OverviewQueries)).HasNoKey();
But always when I try to run a joined table I get the error:
InvalidOperationException: The underlying reader doesn't have as many fields as expected.
When I run it directly I have no issues:
string sqlquery = @"SELECT computerimport.action_id FROM computerimport LEFT OUTER JOIN action ON action.action_id = computerimport.action_id" ;
_context.OverviewQueries.FromSqlRaw(sqlquery).ToList();
Update:
Ok, the model has to have exact the same values which you want to pass, it can not have more:
string sqlquery = "SELECT computerimport.*, action.action_name, STRING_AGG(TRIM(collection.collection_name), ' - ') AS collection_name, STRING_AGG(TRIM(collection.collection_displayname), ' - ') AS collection_displayname, result.result_name " +
"FROM computerimport " +
"LEFT OUTER JOIN action ON action.action_id = computerimport.action_id " +
"LEFT OUTER JOIN result ON result.result_id = computerimport.result_id " +
"LEFT OUTER JOIN computercollection ON computerimport.computer_id = computercollection.computer_id " +
"LEFT OUTER JOIN collection ON computercollection.collection_id = collection.collection_id " +
"GROUP BY computerimport.computer_id, computerimport.hostname, computerimport.uuid, computerimport.daterequested, computerimport.requester, computerimport.daterealized, computerimport.action_id, action_name, computerimport.result_id, result_name, computerimport.comment";
var ovmraw = await _context.OverviewQueries.FromSqlRaw(sqlquery).ToListAsync();
OverviewQueries
public int computer_id { get; set;}
public string hostname {get; set;}
public string uuid {get; set;}
public Nullable<DateTime> daterequested {get; set;}
public Nullable<DateTime> daterealized {get; set;}
public string requester {get; set;}
public int action_id {get; set;}
public int result_id {get; set;}
public string comment {get; set;}
// Values from other models
public string action_name {get; set;}
//public int? collection_id { get; set; }
public string collection_name {get; set;
public string collection_displayname {get; set;}
public string result_name {get; set;}
