Converting a Union Query to a LINQ to Entity Query
Can anyone help me with converting this query to Linq query to objects as appropriate. I am new to Linq and want to write these queries correctly. This is quite in demand for what I am doing with UNION and subqueries in it
SELECT pf.FileID, pf.ServerName, pf.MigrationType
FROM pOrders pf
WHERE pf.FileID IN (select GCMFileID FROM Signals
where SignalFileID = " + FileID + ")
UNION
SELECT pf.FileID, pf.ServerName, pf.MigrationType
FROM pOrders pf
WHERE pf.FileID = " + FileID + "
order by pf.MigrationType desc
source to share
I know I saw the comments ... but
var signalIds = Signals.Where(s => s.SignalFileId = FILEID).Select(x => x.GCMFileID ).ToArray();
pOrders.Where(pf => signalIds.Contains(pf.FileID))
.Union(
pOrders.Where(pf => pf.FileID == FILEID))
.OrderByDescending(u => u.MigrationType)
.Select(u => new {u.FileID, u.ServerName, u.MigrationType});
source to share
var innerquery = from t in db.Signals
where t.SignalFileID == FileID
select new {t.SignalFieldID};
var query = (from p in db.pOrders
where p.FieldID.Contains(innerquery.SignalFieldID)
select new {p.FileID, p.ServerName, p.MigrationType}).Union
(from p in db.pOrders
where p.FieldID ==FieldID
orderby p.MigrationType
select new {p.FileID, p.ServerName, p.MigrationType})
source to share
I know this is an old question, but I decided to add my two cents, hoping I could save some time for someone who thinks that, as I originally did, Union () is the correct method to use.
My first mistake was creating a custom comparator with my entity boolean keys after I encountered my first error that the xml column type cannot be used in isolation. Then Linq to Entities complained that it didn't recognize Union (). I notice the accepted answer for calls to ToArray. This brings all the results of the first query into memory before performing the join. The OP wants Linq for entities, so you need to act on IQueryable. Use Concat. The whole query will run against the database.
var innerquery = (from t in db.Signals
where t.SignalFileID == FileID
select t.SignalFileID);
var query = (from p in db.pOrders
where innerquery.Contains(p.FileID)
select new {p.FileID, p.ServerName, p.MigrationType})
.Concat(from p in db.pOrders
where p.FileID == FileID
select new {p.FileID, p.ServerName, p.MigrationType})
.OrderBy(o => o.MigrationType);
source to share