Como: Filtrar resultados de consulta usando o LINQ (Visual Basic)

Language-Integrated Query (LINQ) makes it easy to access database information and execute queries.

O exemplo a seguir mostra como criar um novo aplicativo que executa a consulta contra os resultados de filtros e um de de SQL banco de dados por um valor específico usando o Where cláusula. For more information, see Clúasula Where (Visual Basic).

The examples in this topic use the Northwind sample database. If you do not have the Northwind sample database on your development computer, you can download it from the Microsoft Download Center Web site. For instructions, see Downloading Sample Databases (LINQ to SQL).

Observação
Seu computador pode mostrar nomes ou locais diferentes para alguns dos elementos da interface do usuário do Visual Studio nas instruções a seguir. A edição do Visual Studio que você possui e as configurações que você usa determinam esses elementos. Para obter mais informações, consulte Configurações do Visual Studio.

To create a connection to a database

1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

3. Name the file northwind.dbml. Click Add. O Object Relational Designer (Designer Relacional de Objetos) abre o arquivo northwind.dbml.

To add tables to query to the O/R Designer

1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.

   Se você tiver fechado o Designer Relacional de Objetos, poderá reabri-lo por duas-clicando na northwind.dbml o arquivo que você adicionou anteriormente.

2. Click the Customers table and drag it to the left pane of the designer. Click the Orders table and drag it to the left pane of the designer.

   The designer creates new Customer and Order objects for your project. Notice that the designer automatically detects relationships between the tables and creates child properties for related objects. For example, IntelliSense will show that the Customer object has an Orders property for all orders related to that customer.

3. Save your changes and close the designer.

4. Save your project.

To add code to query the database and display the results

1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

2. Double-click Form1 to add code to the Load event of the form.

3. When you added tables to the O/R Designer, the designer added a DataContext object for your project. This object contains the code that you must have to access those tables, in addition to individual objects and collections for each table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named northwindDataContext.

   You can create an instance of the DataContext in your code and query the tables specified by the O/R Designer.

   Adicionar o seguinte código para o Loaddeevento para consulta as tabelas que são expostas como propriedades de seu contextode dados. Filtra os resultados de consulta e retorna somente os clientes estão localizados em London.

   Dim db As New northwindDataContext
   
   Dim customers_London = From cust In db.Customers 
                          Where cust.City = "London" 
                          Select cust.CustomerID, cust.CompanyName, 
                                 OrderCount = cust.Orders.Count, 
                                 cust.City, cust.Country
   
   DataGridView1.DataSource = customers_London
   

4. Press F5 to run your project and view the results.

5. A seguir estão alguns filtros que você pode experimentar.

   Dim companies_H = From cust In db.Customers 
                     Where cust.Orders.Count > 0 And
                           cust.CompanyName.StartsWith("H") 
                     Select cust.CustomerID, cust.CompanyName, 
                            OrderCount = cust.Orders.Count, 
                            cust.Country
   
   Dim customers_USA = From cust In db.Customers 
                       Where cust.Orders.Count > 15 And
                             cust.Country = "USA" 
                       Select cust.CustomerID, cust.CompanyName, 
                              OrderCount = cust.Orders.Count, 
                              cust.Country
   

Consulte também

Tarefas

Walkthrough: Creating LINQ to SQL Classes (O/R Designer)

Conceitos

DataContext Methods (O/R Designer)

Outros recursos

LINQ no Visual Basic

Consultas(Visual Basic)

LINQ to SQL