I have a linq query, that selects RecentCheckParameterValues (RCPV), and Alarms for every RCPV, if they exist. It selects too much entries due to how i join Alarms to RCPV. I want the query to select a single RCPV - alarm row, that matches complex key for every RCPV, that has a matching alarm. Here is the query:
var query = (from recentCheck in db.EventFrame
from recentCheckLine in db.EventFrameAttribute.Where(x => x.EventFrameId == recentCheck.ObjectId && x.Name == “Линия”).DefaultIfEmpty()
from recentCheckMachine in db.EventFrameAttribute.Where(x => x.EventFrameId == recentCheck.ObjectId && x.Name == “Машина”).DefaultIfEmpty()
from recentCheckAcquisition in db.EventFrameAttribute.Where(x => x.EventFrameId == recentCheck.ObjectId && x.Name == “Название проверки”).DefaultIfEmpty()
from recentCheckAcquisitionClass in db.EventFrameAttribute.Where(x => x.EventFrameId == recentCheck.ObjectId && x.Name == “Класс проверки”).DefaultIfEmpty()
from recentCheckCrew in db.EventFrameAttribute.Where(x => x.EventFrameId == recentCheck.ObjectId && x.Name == “Смена”).DefaultIfEmpty()

from recentCheckChildren in db.EventFrameLinks.Where(x => x.ParentEventFrameId == recentCheck.ObjectId).DefaultIfEmpty()
from recentCheckParameters in db.EventFrame.Where(x => x.ObjectId == recentCheckChildren.EventFrameId && x.Name == “Параметры последних проверок”).DefaultIfEmpty()
from recentCheckParametersChidren in db.EventFrameLinks.Where(x => x.ParentEventFrameId == recentCheckParameters.ObjectId).DefaultIfEmpty()

from RecentCheckParameterValues in db.EventFrame.Where(x => x.ObjectId == recentCheckParametersChidren.EventFrameId && x.Name == “Значения параметров последних проверок”).DefaultIfEmpty()

from RCPVDateEFA in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Дата изменения”)
from RCPVDateEFAV in db.EventFrameAttributeValue.Where(x => x.EventFrameAttributId == RCPVDateEFA.ObjectId && x.Value == RCPVDateEFA.Value && x.ObjectId == RCPVDateEFA.LastValueId)

from RCPVProduct in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Продукт”)
from RCPVParameter in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Параметр”)
from RCPVValue in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Значение”)
from RCPVHead in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Номер головки”)
from RCPVSample in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Номер образца”)
from RCPVPosition in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Номер позиции”)
from RCPVLSL in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Нижняя граница спецификации”).DefaultIfEmpty()
from RCPVUSL in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Верхняя граница спецификации”).DefaultIfEmpty()
from RCPVTarget in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Целевое значение”).DefaultIfEmpty()
from RCPVMachine in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Машина”)
from RCPVUser in db.EventFrameAttribute.Where(x => x.EventFrameId == RecentCheckParameterValues.ObjectId && x.Name == “Пользователь”)

join sub in ( from alarmEf in alarmQuery select new AlarmDTO
{
ObjectId = alarmEf.ObjectId != null ? alarmEf.ObjectId : Guid.Empty,
VariableTypeGroup = “”,
Parameter = alarmEf.alarmParameter,
Product = alarmEf.alarmProduct,
Status = alarmEf.alarmStatus,
Head = alarmEf.alarmHead,
Sample = alarmEf.alarmSample,
Position = alarmEf.alarmPosition,
Value = alarmEf.alarmValue,
}) on
new {
Parameter = RCPVParameter.Value,
Product = RCPVProduct.Value,
Value = RCPVValue.Value,
Head = RCPVHead.Value,
Sample = RCPVSample.Value,
Position = RCPVPosition.Value,
}
equals
new {
Parameter = sub.Parameter ?? “”,
Product = sub.Product ?? “”,
Value = sub.Value ,
Head = sub.Head ?? “”,
Sample = sub.Sample ?? “”,
Position = sub.Position
} into Alarms
from alarmData in Alarms.DefaultIfEmpty()
where (recentCheck.Name == “Последние проверки”) &&
(recentCheck.IsDeleted == false) &&
(recentCheckParameters.IsDeleted == false) &&
(RecentCheckParameterValues.IsDeleted == false) &&

(request.Machine == null ? true : (RCPVMachine.Value == request.Machine)) &&
(request.MachineGroup == null ? true : machinesMG.Contains(recentCheckMachine.Value)) &&
(request.MachineType == null ? true : machinesMT.Contains(recentCheckMachine.Value)) &&
(request.Line == null ? true : (recentCheckLine.Value == request.Line)) &&
(request.AcquisitionClass == null ? true : (recentCheckAcquisitionClass.Value == request.AcquisitionClass)) &&
(request.Acquisition == null ? true : (recentCheckAcquisition.Value == request.Acquisition)) &&
(request.AcquisitionSearch == null ? true : (recentCheckAcquisition.Value.Contains(request.AcquisitionSearch))) &&
(request.Product == null ? true : (RCPVProduct.Value == request.Product)) &&
(request.SpecType == null ? true : shortSpecsFilteredBySpecType.Contains(String.Concat(RCPVProduct.Value, " ", RCPVParameter.Value))) &&
(request.Variable == null ? true : (RCPVParameter.Value == request.Variable)) &&
(request.VariableSearch == null ? true : RCPVParameter.Value.Contains(request.VariableSearch)) &&
(request.Crew == null ? true : (recentCheckCrew.Value == request.Crew)) &&
(request.User == null ? true : (RCPVUser.Value == request.User)) &&
(fromDate == null ? true : RCPVDateEFAV.ValueDateTime >= fromDate) &&
(toDate == null ? true : RCPVDateEFAV.ValueDateTime < toDate) //&&
select

new OOSTrendsDTO
{
RecentCheckId = recentCheck.ObjectId,
RCPObjectId = recentCheckParameters.ObjectId,
RCPVObjectId = RecentCheckParameterValues.ObjectId,
RCPVParameter = RCPVParameter.Value,
RCPVProduct = RCPVProduct.Value,
RCPVValue = RCPVValue.Value,
RCPVHead = RCPVHead.Value,
RCPVSample = RCPVSample.Value,
RCPVPosition = RCPVPosition.Value,
RCPVLSL = RCPVLSL.Value,
RCPVUSL = RCPVUSL.Value,
RCPVTarget = RCPVTarget.Value,

PeriodGroup = “”,
LineMachineGroup = “”,
VariableProductGroup = “”,

AlarmId = alarmData.ObjectId,
AlarmDate = alarmData.AlarmDate ,
AlarmLine = recentCheckLine.Value ?? “”,
AlarmMachine = recentCheckMachine.Value ?? “”,

}) ;
I wanted to add .Take(1) after Alarms.DefaultIfEmpty(), but it does not seem to work, because Alarms is not queriable. Adding .AsQuariabble to Alarms didnt help either