How does the Where logic in the Entity Framework work?

11

I'm studying C # and just did my first query in the Data Bank:

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 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 did not get to look at any tutorial, but I tried to make the query "in luck" and I ended up getting it.

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

But here some doubts have arisen:

  • Whenever I want to use Where , I just do something equivalent (that is, what I would do in Mysql , translating to C# ) within that lambda returning true or% how do I get the data I want to return?

  • If I wanted users who did not have the word false , would it suffice to "deny" Wallace ?

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

asked by anonymous 28.04.2016 / 19:42

2 answers

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

  • Denying the result of Contains() is one way to resolve this. There may be more interesting ways depending on what you want, but for the basics, in simple example, that's it.

LINQ has a sophisticated mechanism for creating expression trees to generate string SQL query. Obviously every database vendor needs to have a provider for LINQ "teaching" it how the expression tree should generate an appropriate SQL command . The same would apply to 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 on the Code Project about this ( Part 1 , Part 2 , Part 3 ).

You have another Microsoft tutorial on authoring of providers of IQUeryable .

LINQPad can be your friend. Although it is possible to look through the debugger of Visual Studio if you just want to see how the query was generated.

Note that LINQ is something much more comprehensive than the Where used in the title of the question. In this specific case there will be a SELECT generation with WHERE , ORDER BY e LIMIT . Importante notar também que o ToList () 'is used to make the list. This is where the query will take effect.

    
28.04.2016 / 20:04
3

Let's go

R: Correct

If I wanted users who did not have the word Wallace, would it suffice to "deny" the Contains?

R: Correct

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

A: It translates LAMBDA into the bank language (MYSQL), so sometimes you can not get some lambda query when you are searching the database

    
28.04.2016 / 20:34