Convert sql to Linq

You can try to rewrite the code with lambdas to code without lambdas (:

Do not make the mistake of asserting that the presence of lambdas implies the absence of LINQ expressions. Remember that the extension methods Select , Where , among others, are all part of LINQ; The great detail is that in one case you can use these methods in a style similar to SQL and in another case they are used as a cascade call of the different extension methods. Whatever the case, the MSIL code generated when compiling is practically the same, which is expressed as a cascade call of the different extension methods.

Based on the LINQ code with lambdas, as in the question:

var cursos_tomados = ctx.AlumnoCurso.Where(x => x.Alumno_id == ((Alumno_id)))
                      .Select(x => x.Curso_id)
                      .ToList();
cursos = ctx.Curso.Where(x => !cursos_tomados.Contains(x.id)).ToList();

As written, Alumno_id (in double parentheses) is a variable whose value is previously defined. This is not seen in the original SQL query, maybe you missed it?

As I see it, the objective is to first obtain all the courses id that the Alumno_id has assigned. Then, get all the courses that the Alumno_id does not have assigned (by subtracting sets).

To put it in a more understandable way, what you want is: given a Alumno_id corresponding to a Alumno , get all the Cursos that not are assigned to that student. If the goal you are pursuing not is the one I have described so far, leave a comment to know more precisely what you want to achieve.

The first instruction in the LINQ expression above can be rewritten as:

var cursosTomadosIds = (from cursoTomado in ctx.AlumnoCurso
                       where cursoTomado.Alumno_id == Alumno_id
                       select cursoTomado.Curso_id).ToList();

And the second instruction can be rewritten as:

var cursosNoTomados = (from curso in ctx.Curso
                      where !cursosTomadosIds.Contains(curso.id)).ToList();

You can combine both LINQ expressions with the help of the let statement:

var cursosNoTomados = (from curso in ctx.Curso
                      let cursosTomadosIds = (from cursoTomado in ctx.AlumnoCurso
                                             where cursoTomado.Alumno_id == Alumno_id
                                             select cursoTomado.Curso_id).ToList()
                      where !cursosTomadosIds.Contains(curso.id)).ToList();

In this particular case, the combined expression is much less efficient than the expressions separately, since the expression let is re-evaluated for each entry in the enumeration generated by the LINQ code. I recommend do not use the LINQ expression combined.

A left join also works. You can define it as:

var query = from curso in ctx.Curso
            join alumnoCurso in (from ac in ctx.AlumnoCurso
                                 where ac.Alumno_id == ((Alumno_id))
                                 select ac)
                             on curso.id equals alumnoCurso.Curso_id into g
            where !g.Any()
            select curso;

Note the presence of a sub-query that contains ((Alumno_id)) in the previous code. Alumno_id (in double parentheses) must be a variable whose value is previously defined.

Any intrigue, do not hesitate to leave a comment!

I add an extender method for LeftsJoins in LinQ, and the example of how to use it, in case it's worth someone:

public static IEnumerable<TResult> 
    LeftJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
                                                  IEnumerable<TInner> inner, 
                                                  Func<TSource, TKey> pk, 
                                                  Func<TInner, TKey> fk, 
                                                  Func<TSource, TInner, TResult> result)
{
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();

    _result = from s in source
          join i in inner
          on pk(s) equals fk(i) into joinData
          from left in joinData.DefaultIfEmpty()
          select result(s, left);

    return _result;
}  



var resultJoint = Person.BuiltPersons().LeftJoin(                    /// Source Collection
                    Address.BuiltAddresses(),                        /// Inner Collection
                    p => p.IdAddress,                                /// PK
                    a => a.IdAddress,                                /// FK
                    (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection
                .Select(a => new
                {
                    Name             = a.MyPerson.Name,
                    Age              = a.MyPerson.Age,
                    PersonIdAddress  = a.MyPerson.IdAddress,
                    AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
                    Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")
                }); 

In linq you can implement the left join

How to: Perform left outer join operations

it could be something like this

 var query = from curso in dbcontext.Curso
            join alumno in dbcontext.AlumnoCurso on curso.id equals alumno.Curso_id into g
            where g.DefaultIfEmpty() == null
            select curso;