c# EntityFrameworkCore помогите с оптимизацией
Подскажите пожалуйста, как мне написать запрос в EF, чтобы всю функцию сортировки возложить на базу данных. Сейчас Моя программа потребляет слишком много ресурсов процессора.
public class RawMilk
{
public async Task<BindingSource> select()
{
BindingSource binding = new BindingSource();
try
{
await Task.Run(() =>
{
Debug.WriteLine("Start read");
using var db = new ConnectionMilkoscan();
var samples = db.sample.AsNoTracking().OrderByDescending(e => e.SampNo).Take(1000).ToList();
var prod = db.product.AsNoTracking().ToList();
var preds7 = queryPrediction(7);
var preds8 = queryPrediction(8);
var preds9 = queryPrediction(9);
var preds10 = queryPrediction(10);
var preds11 = queryPrediction(11);
var preds31 = queryPrediction(31);
var preds37 = queryPrediction(37);
var preds41 = queryPrediction(41);
var preds47 = queryPrediction(47);
var preds55 = queryPrediction(55);
var preds63 = queryPrediction(63);
Debug.WriteLine("Read end. Start write");
var millkoscan =
(from sample in samples
join product in prod on sample.ProdRef equals product.ProdNo
join pred7 in preds7 on sample.SampNo equals pred7.SampRef
join pred8 in preds8 on sample.SampNo equals pred8.SampRef
join pred9 in preds9 on sample.SampNo equals pred9.SampRef
join pred10 in preds10 on sample.SampNo equals pred10.SampRef
join pred11 in preds11 on sample.SampNo equals pred11.SampRef
join pred31 in preds31 on sample.SampNo equals pred31.SampRef
join pred37 in preds37 on sample.SampNo equals pred37.SampRef
join pred41 in preds41 on sample.SampNo equals pred41.SampRef
join pred47 in preds47 on sample.SampNo equals pred47.SampRef
join pred55 in preds55 on sample.SampNo equals pred55.SampRef
join pred63 in preds63 on sample.SampNo equals pred63.SampRef
where pred8.RepNoRef == pred7.RepNoRef
& pred9.RepNoRef == pred7.RepNoRef
& pred10.RepNoRef == pred7.RepNoRef
& pred11.RepNoRef == pred7.RepNoRef
& pred31.RepNoRef == pred7.RepNoRef
& pred37.RepNoRef == pred7.RepNoRef
& pred41.RepNoRef == pred7.RepNoRef
& pred47.RepNoRef == pred7.RepNoRef
& pred55.RepNoRef == pred7.RepNoRef
& pred63.RepNoRef == pred7.RepNoRef
select new
{
No = sample.SampNo,
a = sample.DateTime,
b = sample.SampleId,
c = product.Name,
d = pred7.RepNoRef,
e = pred7.Value,
f = pred8.Value,
g = pred63.Value,
h = pred31.Value,
j = pred47.Value,
k = pred11.Value,
l = pred9.Value,
m = pred10.Value,
n = pred37.Value,
o = pred41.Value,
p = pred55.Value
}
).ToList();
Debug.WriteLine("write end 1");
binding.DataSource = millkoscan;
Debug.WriteLine("write end2");
});
Debug.WriteLine("write end3");
return binding;
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
return binding;
}
}
private static List<PredictionC> queryPrediction(int compRef, int limit = 1000)
{
using var db = new ConnectionMilkoscan();
return (from pred in db.prediction where pred.CompRef == compRef select pred).AsNoTracking().OrderByDescending(e => e.SampRef).Take(limit).ToList();
}
}
Вот пример SQL запроса который я хочу сделать в Entity Framework core
select i.SampNo, i.`DateTime`, i.SampleId, prod.`Name`, x.RepNoRef, x.Fat, x.protein, x.snf, x.ts, x.lactose, x.freezing, x.Acidity,
x.density, x.Urea, x.Cyanuricacid, x.Formaldehyde, x.Hydroxyproline, x.Maltodextrine, x.Melamine, x.Sodium,
x.Sodiumnitrite, x.Sucrose, x.NBA, x.Protein_I
from
`millkoscan`.`Sample` i ,
(select a.SampRef, a.RepNoRef, a.`Value` as Fat, b.`Value` as protein, c.`Value` as snf, d.`Value` as ts, e.`Value` as lactose,
f.`Value` as freezing, g.`Value` as Acidity, h.`Value` as density, i.`Value` as Urea, j.`Value` as Cyanuricacid,
k.`Value` as Formaldehyde, l.`Value` as Hydroxyproline, m.`Value` as Maltodextrine, n.`Value` as Melamine,
o.`Value` as Sodium, p.`Value` as Sodiumnitrite, q.`Value` as Sucrose, r.`Value` as NBA, s.`Value` as Protein_I
from
(select * from `millkoscan`.`Prediction` where CompRef = 7) a,
(select * from `millkoscan`.`Prediction` where CompRef = 8) b,
(select * from `millkoscan`.`Prediction` where CompRef = 9) c,
(select * from `millkoscan`.`Prediction` where CompRef = 10) d,
(select * from `millkoscan`.`Prediction` where CompRef = 11) e,
(select * from `millkoscan`.`Prediction` where CompRef = 31) f,
(select * from `millkoscan`.`Prediction` where CompRef = 37) g,
(select * from `millkoscan`.`Prediction` where CompRef = 41) h,
(select * from `millkoscan`.`Prediction` where CompRef = 47) i,
(select * from `millkoscan`.`Prediction` where CompRef = 55) j,
(select * from `millkoscan`.`Prediction` where CompRef = 56) k,
(select * from `millkoscan`.`Prediction` where CompRef = 57) l,
(select * from `millkoscan`.`Prediction` where CompRef = 58) m,
(select * from `millkoscan`.`Prediction` where CompRef = 59) n,
(select * from `millkoscan`.`Prediction` where CompRef = 60) o,
(select * from `millkoscan`.`Prediction` where CompRef = 61) p,
(select * from `millkoscan`.`Prediction` where CompRef = 62) q,
(select * from `millkoscan`.`Prediction` where CompRef = 63) r,
(select * from `millkoscan`.`Prediction` where CompRef = 1075) s
where b.SampRef = a.SampRef and b.RepNoRef = a.RepNoRef and
c.SampRef = a.`SampRef` and c.`RepNoRef` = a.RepNoRef and
d.SampRef = a.`SampRef` and d.`RepNoRef` = a.RepNoRef and
e.SampRef = a.`SampRef` and e.`RepNoRef` = a.RepNoRef and
f.SampRef = a.`SampRef` and f.`RepNoRef` = a.RepNoRef and
g.SampRef = a.`SampRef` and g.`RepNoRef` = a.RepNoRef and
h.SampRef = a.`SampRef` and h.`RepNoRef` = a.RepNoRef and
i.SampRef = a.`SampRef` and i.`RepNoRef` = a.RepNoRef and
j.SampRef = a.`SampRef` and j.`RepNoRef` = a.RepNoRef and
k.SampRef = a.`SampRef` and k.`RepNoRef` = a.RepNoRef and
l.SampRef = a.`SampRef` and l.`RepNoRef` = a.RepNoRef and
m.SampRef = a.`SampRef` and m.`RepNoRef` = a.RepNoRef and
n.SampRef = a.`SampRef` and n.`RepNoRef` = a.RepNoRef and
o.SampRef = a.`SampRef` and o.`RepNoRef` = a.RepNoRef and
p.SampRef = a.`SampRef` and p.`RepNoRef` = a.RepNoRef and
q.SampRef = a.`SampRef` and q.`RepNoRef` = a.RepNoRef and
r.SampRef = a.`SampRef` and r.`RepNoRef` = a.RepNoRef and
s.SampRef = a.`SampRef` and s.`RepNoRef` = a.RepNoRef
) x, `millkoscan`.`Product` prod
Where i.SampNo = x.SampRef and prod.ProdNo = i.ProdRef
Ответы (2 шт):
Автор решения: Oleksandr Kostian
→ Ссылка
Извлекать из нескольких таблиц можно так:
var players = new[] { "Tom", "Jay", "Mary" }.AsQueryable();
IEnumerable<string> query =
from name1 in players
from name2 in players
select name1 + " vs " + name2;
в селекте можно создавать тип, например анонимный
select new { name1, name2 };
Автор решения: AlexASU
→ Ссылка
Исправил код, переложил все выполнение команды на MySQL server, но все стало только хуже, MySQL не может обработать запрос, стал отслеживать, запросы, которые прилетают в MySQL и вот что получилось:
Моу код в c#
public class RawMilk
{
public void select()
{
Console.WriteLine("Старт");
try
{
using var db = new milkoscanDB();
var samples = (from Samp in db.sample select Samp);
var preds7 = queryPrediction(7, db);
var preds8 = queryPrediction(8, db);
var preds9 = queryPrediction(9, db);
var preds10 = queryPrediction(10, db);
var preds11 = queryPrediction(11, db);
var preds31 = queryPrediction(31, db);
var preds37 = queryPrediction(37, db);
var preds41 = queryPrediction(41, db);
var preds47 = queryPrediction(47, db);
var preds55 = queryPrediction(55, db);
var preds56 = queryPrediction(56, db);
var preds57 = queryPrediction(57, db);
var preds58 = queryPrediction(58, db);
var preds59 = queryPrediction(59, db);
var preds60 = queryPrediction(60, db);
var preds61 = queryPrediction(61, db);
var preds62 = queryPrediction(62, db);
var preds63 = queryPrediction(63, db);
var preds1075 = queryPrediction(1075, db);
var millkoscan =
(
from pred7 in preds7
from pred8 in preds8
from pred9 in preds9
from pred10 in preds10
from pred11 in preds11
from pred31 in preds31
from pred37 in preds37
from pred41 in preds41
from pred47 in preds47
from pred55 in preds55
from pred63 in preds63
where pred8.SampRef == pred7.SampRef
& pred9.SampRef == pred7.SampRef
& pred10.SampRef == pred7.SampRef
& pred11.SampRef == pred7.SampRef
& pred31.SampRef == pred7.SampRef
& pred37.SampRef == pred7.SampRef
& pred41.SampRef == pred7.SampRef
& pred47.SampRef == pred7.SampRef
& pred55.SampRef == pred7.SampRef
& pred63.SampRef == pred7.SampRef
& pred8.RepNoRef == pred7.RepNoRef
& pred9.RepNoRef == pred7.RepNoRef
& pred10.RepNoRef == pred7.RepNoRef
& pred11.RepNoRef == pred7.RepNoRef
& pred31.RepNoRef == pred7.RepNoRef
& pred37.RepNoRef == pred7.RepNoRef
& pred41.RepNoRef == pred7.RepNoRef
& pred47.RepNoRef == pred7.RepNoRef
& pred55.RepNoRef == pred7.RepNoRef
& pred63.RepNoRef == pred7.RepNoRef
select new
{
ID = pred7.SampRef,
stateAnalys = pred7.RepNoRef,
fat = pred7.Value,
protein = pred8.Value,
snf = pred9.Value,
ts = pred10.Value,
lactose = pred11.Value,
freezing = pred31.Value,
Acidity = pred37.Value,
d = pred41.Value,
e = pred47.Value,
f = pred55.Value,
g = pred63.Value
}).Take(1000).ToList();
Console.WriteLine("Тест список");
foreach (var Milkoscan in millkoscan)
{
Console.WriteLine(
$"{Milkoscan.ID}: " +
$"{Milkoscan.stateAnalys}: " +
$"{Milkoscan.fat}: " +
$"{Milkoscan.protein}: " +
$"{Milkoscan.snf} : " +
$"{Milkoscan.ts} : " +
$"{Milkoscan.lactose}" +
$"{Milkoscan.freezing}" +
$"{Milkoscan.Acidity}" +
$"{Milkoscan.d}" +
$"{Milkoscan.e}" +
$"{Milkoscan.f}" +
$"{Milkoscan.g}"
);
}
Console.WriteLine($"Конец");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public static IQueryable<Prediction> queryPrediction(int compRef, milkoscanDB db, int limit = 1000)
{
return (from pred in db.prediction where pred.CompRef == compRef select pred);
}
}
А вот это то, что залетает в виде запроса в MySQL
SELECT `p`.`SampRef` AS `ID`, `p`.`RepNoRef` AS `stateAnalys`, `p`.`Value` AS `fat`, `t`.`Value` AS `protein`, `t0`.`Value` AS `snf`, `t1`.`Value` AS `ts`, `t2`.`Value` AS `lactose`, `t3`.`Value` AS `freezing`, `t4`.`Value` AS `Acidity`, `t5`.`Value` AS `d`, `t6`.`Value` AS `e`, `t7`.`Value` AS `f`, `t8`.`Value` AS `g`
FROM `prediction` AS `p`
CROSS JOIN (
SELECT `p0`.`SampRef`, `p0`.`RepNoRef`, `p0`.`CompRef`, `p0`.`Flags`, `p0`.`Value`
FROM `prediction` AS `p0`
WHERE `p0`.`CompRef` = 8
) AS `t`
CROSS JOIN (
SELECT `p1`.`SampRef`, `p1`.`RepNoRef`, `p1`.`CompRef`, `p1`.`Flags`, `p1`.`Value`
FROM `prediction` AS `p1`
WHERE `p1`.`CompRef` = 9
) AS `t0`
CROSS JOIN (
SELECT `p2`.`SampRef`, `p2`.`RepNoRef`, `p2`.`CompRef`, `p2`.`Flags`, `p2`.`Value`
FROM `prediction` AS `p2`
WHERE `p2`.`CompRef` = 10
) AS `t1`
CROSS JOIN (
SELECT `p3`.`SampRef`, `p3`.`RepNoRef`, `p3`.`CompRef`, `p3`.`Flags`, `p3`.`Value`
FROM `prediction` AS `p3`
WHERE `p3`.`CompRef` = 11
) AS `t2`
CROSS JOIN (
SELECT `p4`.`SampRef`, `p4`.`RepNoRef`, `p4`.`CompRef`, `p4`.`Flags`, `p4`.`Value`
FROM `prediction` AS `p4`
WHERE `p4`.`CompRef` = 31
) AS `t3`
CROSS JOIN (
SELECT `p5`.`SampRef`, `p5`.`RepNoRef`, `p5`.`CompRef`, `p5`.`Flags`, `p5`.`Value`
FROM `prediction` AS `p5`
WHERE `p5`.`CompRef` = 37
) AS `t4`
CROSS JOIN (
SELECT `p6`.`SampRef`, `p6`.`RepNoRef`, `p6`.`CompRef`, `p6`.`Flags`, `p6`.`Value`
FROM `prediction` AS `p6`
WHERE `p6`.`CompRef` = 41
) AS `t5`
CROSS JOIN (
SELECT `p7`.`SampRef`, `p7`.`RepNoRef`, `p7`.`CompRef`, `p7`.`Flags`, `p7`.`Value`
FROM `prediction` AS `p7`
WHERE `p7`.`CompRef` = 47
) AS `t6`
CROSS JOIN (
SELECT `p8`.`SampRef`, `p8`.`RepNoRef`, `p8`.`CompRef`, `p8`.`Flags`, `p8`.`Value`
FROM `prediction` AS `p8`
WHERE `p8`.`CompRef` = 55
) AS `t7`
CROSS JOIN (
SELECT `p9`.`SampRef`, `p9`.`RepNoRef`, `p9`.`CompRef`, `p9`.`Flags`, `p9`.`Value`
FROM `prediction` AS `p9`
WHERE `p9`.`CompRef` = 63
) AS `t8`
WHERE (`p`.`CompRef` = 7) AND ((((((((((((((((((((`t`.`SampRef` = `p`.`SampRef`) & (`t0`.`SampRef` = `p`.`SampRef`)) & (`t1`.`SampRef` = `p`.`SampRef`)) & (`t2`.`SampRef` = `p`.`SampRef`)) & (`t3`.`SampRef` = `p`.`SampRef`)) & (`t4`.`SampRef` = `p`.`SampRef`)) & (`t5`.`SampRef` = `p`.`SampRef`)) & (`t6`.`SampRef` = `p`.`SampRef`)) & (`t7`.`SampRef` = `p`.`SampRef`)) & (`t8`.`SampRef` = `p`.`SampRef`)) & (`t`.`RepNoRef` = `p`.`RepNoRef`)) & (`t0`.`RepNoRef` = `p`.`RepNoRef`)) & (`t1`.`RepNoRef` = `p`.`RepNoRef`)) & (`t2`.`RepNoRef` = `p`.`RepNoRef`)) & (`t3`.`RepNoRef` = `p`.`RepNoRef`)) & (`t4`.`RepNoRef` = `p`.`RepNoRef`)) & (`t5`.`RepNoRef` = `p`.`RepNoRef`)) & (`t6`.`RepNoRef` = `p`.`RepNoRef`)) & (`t7`.`RepNoRef` = `p`.`RepNoRef`)) & (`t8`.`RepNoRef` = `p`.`RepNoRef`))
LIMIT 1000
Как с этим бороться, я не представляю. Как можно написать код так, чтобы Entity Framework core выдал мне желанный запрос. Подскажите в какую сторону копать.