missing "AS" #3
Description
I have quite complex model and I will try to find what cause this issue myself but for now what I have. the linq query:
FwSalesForceToSampleXref
.Join(People.Include(z => z.Specimens).ThenInclude(z => z.Aliquots), z => z.SapID, z => z.SapID, (l,r) => new{XRef = l, Person = r})
.Select(z => new{
z.Person,
Samples = FwSamples.Where(zz => zz.SampleName == z.XRef.SampleName).ToArray()
})
.Where(z => z.Person.Specimens.Count() == 1 && z.Person.Specimens.All(zz => zz.Aliquots.Count() == 1) && z.Samples.Count() == 1)
.Select(z => new{
Aliquot = z.Person.Specimens.First().Aliquots.First(),
Sample = z.Samples.First()
})
.Where(z => TryParse.Int64(z.Aliquot.Barcode) == z.Sample.NuncBarCode)in result I have query:
SELECT [t].[ID], [t].[AliquotType_ID], [t].[Barcode], [t].[BarcodeType_ID], [t].[Discarded], [t].[Specimen_ID], [t0].[InternalID], [t0].[BarCodeID], [t0].[CLabID], [t0].[FirstName], [t0].[InfecDisStatus], [t0].[InterfaceKey], [t0].[LastName], [t0].[NumberOfAliquots], [t0].[NuncBarCode], [t0].[SampleDate], [t0].[SampleID], [t0].[SampleTypeId], [t0].[StatusId], [t0].[StoreBarCode], [t0].[TotalCurrentAmount], [t0].[VisitID]
FROM [fw_SalesForceToSampleXRef] AS [f]
INNER JOIN [People] AS [p] ON [f].[AccountID] = [p].[sap_id]
OUTER APPLY (
SELECT TOP(1) [a1].[ID], [a1].[AliquotType_ID], [a1].[Barcode], [a1].[BarcodeType_ID], [a1].[Discarded], [a1].[Specimen_ID]
FROM [Aliquots] AS [a1]
WHERE ((
SELECT TOP(1) [s3].[ID]
FROM [Specimens] AS [s3]
WHERE [p].[ID] = [s3].[person_id]) IS NOT NULL) AND (
SELECT TOP(1) [s4].[ID]
FROM [Specimens] AS [s4]
WHERE [p].[ID] = [s4].[person_id]) = [a1].[Specimen_ID]
) AS [t]
LEFT JOIN (
SELECT [t1].[InternalID], [t1].[BarCodeID], [t1].[CLabID], [t1].[FirstName], [t1].[InfecDisStatus], [t1].[InterfaceKey], [t1].[LastName], [t1].[NumberOfAliquots], [t1].[NuncBarCode], [t1].[SampleDate], [t1].[SampleID], [t1].[SampleTypeId], [t1].[StatusId], [t1].[StoreBarCode], [t1].[TotalCurrentAmount], [t1].[VisitID]
FROM (
SELECT [f2].[InternalID], [f2].[BarCodeID], [f2].[CLabID], [f2].[FirstName], [f2].[InfecDisStatus], [f2].[InterfaceKey], [f2].[LastName], [f2].[NumberOfAliquots], [f2].[NuncBarCode], [f2].[SampleDate], [f2].[SampleID], [f2].[SampleTypeId], [f2].[StatusId], [f2].[StoreBarCode], [f2].[TotalCurrentAmount], [f2].[VisitID], ROW_NUMBER() OVER(PARTITION BY [f2].[SampleID] ORDER BY [f2].[InternalID]) AS [row]
FROM [fw_samples] AS [f2]
) AS [t1]
WHERE [t1].[row] <= 1
) AS [t0] ON [f].[SampleID] = [t0].[SampleID]
WHERE (
SELECT COUNT(*)
FROM [Specimens] AS [s]
WHERE [p].[ID] = [s].[person_id]) = 1 AND NOT EXISTS (
SELECT 1
FROM [Specimens] AS [s0]
WHERE [p].[ID] = [s0].[person_id] AND ((
SELECT COUNT(*)
FROM [Aliquots] AS [a]
WHERE [s0].[ID] = [a].[Specimen_ID]) <> 1 OR ((
SELECT COUNT(*)
FROM [Aliquots] AS [a]
WHERE [s0].[ID] = [a].[Specimen_ID]) IS NULL))) AND (
SELECT COUNT(*)
FROM [fw_samples] AS [f0]
WHERE [f0].[SampleID] = [f].[SampleID]) = 1 AND (TRY_PARSE(CAST((
SELECT TOP(1) [a0].[Barcode]
FROM [Aliquots] AS [a0]
WHERE ((
SELECT TOP(1) [s1].[ID]
FROM [Specimens] AS [s1]
WHERE [p].[ID] = [s1].[person_id]) IS NOT NULL) AND (
SELECT TOP(1) [s2].[ID]
FROM [Specimens] AS [s2]
WHERE [p].[ID] = [s2].[person_id]) = [a0].[Specimen_ID]) AS nvarchar(128))) = (
SELECT TOP(1) [f1].[NuncBarCode]
FROM [fw_samples] AS [f1]
WHERE [f1].[SampleID] = [f].[SampleID]) OR (((
SELECT TOP(1) [a0].[Barcode]
FROM [Aliquots] AS [a0]
WHERE ((
SELECT TOP(1) [s1].[ID]
FROM [Specimens] AS [s1]
WHERE [p].[ID] = [s1].[person_id]) IS NOT NULL) AND (
SELECT TOP(1) [s2].[ID]
FROM [Specimens] AS [s2]
WHERE [p].[ID] = [s2].[person_id]) = [a0].[Specimen_ID]) IS NULL) AND ((
SELECT TOP(1) [f1].[NuncBarCode]
FROM [fw_samples] AS [f1]
WHERE [f1].[SampleID] = [f].[SampleID]) IS NULL)))where TRY_PARSE has no corresponding AS