How does the logic of Where in the Entity Framework work?

Question

I’m studying C# and I just made my first query in the Database:

using System;
using System.Data.Entity.Core;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

    namespace Test.Models
    {
        [Table("usuarios")]
        public class Usuario
        {
            [Key]
            public int Id{ set; get; }

            public string Username {
                set;
                get;
            }

            public string Password {
                set;
                get;
            }

            public string Nome {
                set;
                get;
            }

        }
    }

In my Controller I do like this:

var context = new SimpleContext ();

ViewBag.title = "Página inicial";

var usuarios = context.Usuarios
            .Where ((usuario) => usuario.Nome.Contains ("wallace"))
            .OrderBy((usuario) => usuario.Nome)
            .Take (10)
            .ToList ();

return View (usuarios);

I didn’t get to look at any tutorial, but I was trying to make the query "lucky" and I ended up getting.

I understood so far that in the method Where is expected to pass a lambda. Another thing I understood is that the expression that returns true inside will be the data that will be returned from the table.

But then some doubts arose:

• Whenever I want to use one Where, I just do something equivalent (ie, what I would do in the Mysql, translating to C#) within that lambda returning true or false for the data I want to return?

• If I wanted users who didn’t have the word Wallace, would suffice to "deny" the Contains?

• If the Where needs only one true or false returned by lambda, how does the Entity Framework can use C# Syntax to query Mysql?

Answer 1

• I think the first question is making a correct statement :P

• The negation of the result of Contains() is one way to solve this. There may be more interesting ways depending on what you want, but for the basic, simple example, this is it.

LINQ has a sophisticated mechanism for creating expression trees to generate the string SQL query. Obviously every database provider needs to have a provider for LINQ "teaching" it as the expression tree should generate a suitable SQL command. The same would be true for any type of query that would need to be generated, not just SQL. It is possible for the programmer to manipulate this tree, although of rare use.

There is a tutorial in the Code Project about this (Part 1, Part 2, Part 3).

There’s another Microsoft tutorial on setting up providers of IQUeryable.

The Linqpad can be your friend. Although it is possible to observe the Debugger Visual Studio if you just want to see how the generated query looked.

Note that LINQ is something much more comprehensive than Where used in the title of the question. In this specific case there will be the generation of a SELECT with WHERE, ORDER BY and LIMIT. It is also important to note that the ToList() is used for list completion. It is at this point that the consultation will be carried out, many people use it when they do not need it and lose the performance advantage in some cases.

Answer 2

Come on

Whenever I want to return a Where, I just do something equivalent inside this lambda by returning true or false to the data I want to return?

A:Correct

If I wanted users who didn’t have the word Wallace, it would be enough to "deny" Contains?

A: Correct

If Where only needs a true or false returned by lambda, how can Entity Framework use C# Syntax to query Mysql?

A: It translates LAMBDA to the database language (MYSQL), so sometimes you don’t get some lambda query when searching in the database