Complex SQL to LINQ conversion with subquery

Question

I am trying to convert this expression into LINQ from SQL, but a bit too difficult for me, maybe you can help me with this!

SELECT     TOP (2) RecipeID, UserID, Name, Servings, PreparationTime, TotalTime, DifficultyLevelID, CuisineID, DishID, MainIngredientID, PriceLevelID, FlavourID, Instructions, 
                      Notes, Thumbnail, VideoLink
FROM         dbo.Recipes
WHERE     (RecipeID NOT IN
                          (SELECT DISTINCT Recipes_1.RecipeID
                            FROM          dbo.Allergies INNER JOIN
                                                   dbo.UsersAllergies ON dbo.Allergies.AllergyID = dbo.UsersAllergies.AllergyID INNER JOIN
                                                   dbo.IngredientsAllergies ON dbo.Allergies.AllergyID = dbo.IngredientsAllergies.AllergyID INNER JOIN
                                                   dbo.Ingredients ON dbo.IngredientsAllergies.IngredientID = dbo.Ingredients.IngredientID INNER JOIN
                                                   dbo.RecipesIngredients ON dbo.Ingredients.IngredientID = dbo.RecipesIngredients.IngredientID INNER JOIN
                                                   dbo.Recipes AS Recipes_1 ON dbo.RecipesIngredients.RecipeID = Recipes_1.RecipeID INNER JOIN
                                                   dbo.Users ON dbo.UsersAllergies.UserID = dbo.Users.UserID INNER JOIN
                                                   dbo.AllergyFactors ON dbo.IngredientsAllergies.AllergyFactorID = dbo.AllergyFactors.AllergyFactorID
                            WHERE      (dbo.Users.UserID = 3) AND (dbo.AllergyFactors.AllergyFactorID < 3)))

Answer 1

It would be easier to help you if you showed us what you have already tried, but a Linq expression like this should give you the same result set

var query = (from rec in context.Recipes
            where !(from al in context.Allergies
                    from ua in context.UsersAllergies.Where(x => al.AllergyID == x.AllergyID)
                    from ia in context.IngredientsAllergies.Where(x => al.AllergyID == x.AllergyID)
                    from in in context.Ingredients.Where(x => ia.IngredientID == x.IngredientID)
                    from ri in context.RecipesIngredients.Where(x => in.IngredientID == x.IngredientID)
                    from re in context.Recipes.Where(x => ri.RecipeID == x.RecipeID)
                    from us in context.Users.Where(x => ua.UserID == x.UserID)
                    from af in context.AllergyFactors.Where(x => ia.AllergyFactorID == x.AllergyFactorID)
                    where us.UserID == 3 && af.AllergyFactorID < 3
                    select re.RecipeID)
                    .Distinct()
                    .Contains(rec.RecipeID)
            select new
            {
                rec.RecipeID, 
                rec.UserID, 
                rec.Name, 
                rec.Servings, 
                rec.PreparationTime, 
                rec.TotalTime, 
                rec.DifficultyLevelID, 
                rec.CuisineID, 
                rec.DishID, 
                rec.MainIngredientID, 
                rec.PriceLevelID, 
                rec.FlavourID, 
                rec.Instructions,
                rec.Notes, 
                rec.Thumbnail, 
                rec.VideoLink
            }).Take(2);