检索数据LINQ2SQL点 [英] Linq2Sql point of retrieving data

问题描述

我目前有在其上使用LINQ2SQL作为其数据库访问框架的一个项目工作，现在有很多的LINQ查询这基本上做到以下几点：

  VAR的结果=来自以下; some_table> 
加入< some_other_table> 
加入< another_table> 
选择< some_other_domain_model> //这是一个非LINQ2SQL波苏
 
返回result.Where（小于Some_Predicate>）; 
  

因此，例如，假定你读3个表，然后整理的内容到一个大的更高级别的型号，发送到视图。现在，忽略域的混合，因为这不打扰我太多，其最终的where子句哪些呢。

现在我还没有使用LINQ2SQL竟有如此前会我是说错了什么事情发生的是：

1. 生成基于SQL关从，加盟，加盟，选择LINQ


2. 检索所有行


3. 所有的数据映射到一个大模型（在内存中）


4. 遍历所有的模型和然后返回只适用那些

由于这是我的问题的症结所在，它将使感在我的脑海如果上述流程会发生什么，但它已被人谁显然知道这个框架有很多比4步更好的是某种计入SQL生成，这样就不会被拉回所有记录辩论，但我不知道它怎么可以这样做因为它需要所有的数据预先填充此然后将其应用一个单独的where子句，所以我的第4点的行已经全部阅读承担并已在内存中。

我试图推动他们自己的where子句移动到LINQ，使其在数据库级过滤掉非所需的记录，但是我想知道是否有人能以如果我上面的假设是正确的建议？

== ==编辑

有添加注释，以吸引更多的注意一个事实，即不一个LINQ2SQL生成的对象，是一些随机POCO手卷别处，刚才来缩小我的主要焦点是对这个问题的情况下。由于问题是关于 LESS它身在何处，我把where子句更多关于是否where子句仍然可以分解成底层当它被应用于从LINQ2SQL查询生成未LINQ2SQL对象查询。

下面是什么，我的意思是希望另一个更简洁的例子绘制点更多地转向哪里我不理解的是：

  / * 
我只打算把汽车性能入LINQ2SQL实体，
尽管在真实世界中，他们将私人背
场的公共属性做notiftying的混合。 
 * / 
 
 [全球:: System.Data.Linq.Mapping.TableAttribute（NAME =dbo.some_table_1）] 
公共类SomeLinq2SqlTable1 
 $ { b $ b〔全球:: System.Data.Linq.Mapping.ColumnAttribute（存储=some_table_1_id，自动同步= AutoSync.OnInsert，的DbType =内部NOT NULL IDENTITY，IsPrimaryKey = TRUE，IsDbGenerated = TRUE）] 
酒店的公共INT标识{获取;设置;} 
} 
 
 [全球:: System.Data.Linq.Mapping.TableAttribute（NAME =dbo.some_table_2）] 
公共类SomeLinq2SqlTable2 
 {
 [全球:: System.Data.Linq.Mapping.ColumnAttribute（存储=some_table_2_id，自动同步= AutoSync.OnInsert，的DbType =内部NOT NULL，IsPrimaryKey = TRUE， IsDbGenerated = TRUE）] 
公众诠释标识{获取;设置;} 
 
 [全球:: System.Data.Linq.Mapping.ColumnAttribute（存储=some_table_2_name，自动同步=自动同步。 OnInsert，的DbType =VARCHAR NOT NULL，IsPrimaryKey = FALSE）] 
公共字符串名称{;设置;} 
} 
 
 [全球::将System.Data.Linq .Mapping.TableAttribute（NAME =dbo.some_table_3）] 
公共类SomeLinq2SqlTable3 
 {
 [全球:: System.Data.Linq.Mapping.ColumnAttribute（存储=some_table_3_id自动同步= AutoSync.OnInsert，的DbType =内部NOT NULL，IsPrimaryKey = TRUE，IsDbGenerated = TRUE）] 
公众诠释标识{获取;集;} 
 
 [全球::系统。 Data.Linq.Mapping.ColumnAttribute（存储=some_table_3_other，自动同步= AutoSync.OnInsert，的DbType =VARCHAR NOT NULL，IsPrimaryKey = FALSE）] 
公共字符串其他{获取;设置;} 
 } 
 
 / * 
这是一些手卷波科，无关与LINQ2SQL，认为它是各种各样的
A视图模型。 
 * / 
公共类SomeViewModel 
 {
公众诠释标识{获取;集;} 
公共字符串名称{;设置;} 
公共字符串其他{获取;设置;} 
} 
 
 / * 
这里是psudo查询加入所有的表，然后填充从查询中
视图模型项目终于做一凡的视图模型对象条款
。 
 * / 
 VAR的结果=从// Linq2SqlTable1为T1 
加入// Linq2SqlTable2.id上Linq2SqlTable1.id为T2 
加入// Linq2SqlTable3.id上Linq2SqlTable1.id作为T3 
选择新的ViewModel {ID = t1.Id，名称= t2.Name，其他= t3.Other} 
 
返回result.Where（视图模型=> viewModel.Name.Contains（ 一些-废话））; 
  

于是给定上面的例子中，将最后的Where语句被考虑到下面的查询，或者将所述其中，在视图模型引起检索，然后在内存评价？

很抱歉的详细程度这个问题，但有关于它的非常小的文件，这是一个相当特殊问题。

解决方案

您不需要推其中，条款的任何更高。这是好的地方是，只要结果是的IQueryable< T> （对于某些 T ）。 LINQ是组合的。事实上，有使用LINQ语法使用扩展法语法之间绝对没有区别，无论是和相同会工作。基本上，当你创建一个查询，它的仅建筑是什么一直是模型的要求的。没有什么是的执行的，直到你开始迭代它（的foreach ，了ToList（）等） 。因此，增加一个额外的其中，上到底是罚款：将获得内置于由查询

您可以。通过监控SQL连接验证这一点很干脆;你会看到它包含在TSQL的其中，子句，在SQL服务器的过滤器。

这允许一些有趣的场景，例如灵活的搜索：

 的IQueryable<客户>查询= db.Customers; 
如果（！NAME = NULL）查询= query.Where（X => x.Name ==名）; 
如果（！=区域空）查询= query.Where（X => x.Region ==区）; 
：
如果（DOB！= NULL）查询= query.Where（X => x.DoB == DOB）; 
变种结果= query.Take（50）.ToList（）; 
  

在你的假设方面，他们是不正确的 - 这是真的：

1. 构建组合的查询，从合成（另发），加盟，加盟，选择


2. 进一步编写查询，加入其中（没有上述成分不同）


3. 在某一点后，迭代查询
   
   
   
   1. 从完全由查询
      
   2. retreive行
   
   
   3. 地图分成模式
   
   
   4. 产生的结果
   
   

请注意，当查询迭代SQL生成只发生;在那之前，你可以保持一整天组成它。直到它被重复它不会碰SQL服务器

I am currently having to work on a project which uses linq2sql as its database accessing framework, now there are a lot of linq queries which basically do the following:

var result =    from <some_table>
                join <some_other_table>
                join <another_table>
                select <some_other_domain_model> // This is a non linq2SQL poco

return result.Where(<Some_Predicate>);

So for example assume you read 3 tables, and then collate the contents into one big higher level model, for sending to a view. Now ignore the mixing of domains, as that doesn't bother me too much, its the final where clause which does.

Now I have not used Linq2Sql much before so would I be right in saying what is going to happen is:

1. Generate SQL based off the from, join, join, select linq
2. Retrieve all rows
3. Map all this data into one big model (in memory)
4. Loop through all models and then return only the applicable ones

As this is the crux of my question, it would make sense in my mind if the above flow is what would happen, but it has been debated by people who apparently know the framework a lot better than the 4th step is somehow factored into the SQL generation so it will not be pulling back all records, but I dont know how it could be doing that as it NEEDS all the data up front to populate this which it then applies a separate where clause on, so I assume by the 4th point the rows have all been read and are already in memory.

I am trying to push for them to move their where clause into the linq so that it filters out un-needed records at the database level, however I was wondering if anyone can advise as to if my assumptions above are right?

== Edit ==

Have added comment to draw more attention to the fact that the is not a linq2sql generated object and is some random poco hand rolled elsewhere, just to narrow down where my main focus is on the context of the question. As the question is LESS about "does it matter where I put the where clause" and more about "Does the where clause still get factored into the underlying query when it is applied to a non linq2sql object generated from a linq2sql query".

Here is another more concise example of what I mean hopefully drawing the point more towards where my lack of understanding is:

/*
    I am only going to put auto properties into the linq2sql entities,
    although in the real world they would be a mix of private backing
    fields with public properties doing the notiftying.
*/

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.some_table_1")]
public class SomeLinq2SqlTable1
{
    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="some_table_1_id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
    public int Id {get;set;}
}

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.some_table_2")]
public class SomeLinq2SqlTable2
{
    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="some_table_2_id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]
    public int Id {get;set;}

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="some_table_2_name", AutoSync=AutoSync.OnInsert, DbType="Varchar NOT NULL", IsPrimaryKey=false)]
    public string Name {get;set;}
}

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.some_table_3")]
public class SomeLinq2SqlTable3
{
    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="some_table_3_id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]
    public int Id {get;set;}

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="some_table_3_other", AutoSync=AutoSync.OnInsert, DbType="Varchar NOT NULL", IsPrimaryKey=false)]
    public string Other {get;set;}
}

/*
    This is some hand rolled Poco, has NOTHING to do with Linq2Sql, think of it as 
    a view model of sorts.
*/
public class SomeViewModel
{
    public int Id {get;set;}
    public string Name {get;set;}
    public string Other {get;set;}
}

/*
    Here is psudo query to join all tables, then populate the
    viewmodel item from the query and finally do a where clause
    on the viewmodel objects.
*/
var result =    from // Linq2SqlTable1 as t1
                join // Linq2SqlTable2.id on Linq2SqlTable1.id as t2
                join // Linq2SqlTable3.id on Linq2SqlTable1.id as t3
                select new ViewModel { Id = t1.Id, Name = t2.Name, Other = t3.Other }

return result.Where(viewModel => viewModel.Name.Contains("some-guff"));

So given the example above, will the final Where statement be factored into the underlying query, or will the where on the viewModel cause a retrieval and then evaluate in memory?

Sorry for the verbosity to this question but there is very little documentation about it, and this is quite a specific question.

解决方案

You do not need to push the Where clause any higher. It is fine where it is, as long as result is IQueryable<T> (for some T). LINQ is composable. Indeed, there's absolutely no difference between using the LINQ syntax as using the extension-method syntax, and either would work identically. Basically, when you create a query, it is only building a model of what has been requested. Nothing is executed until you start iterating it (foreach, ToList(), etc). So adding an extra Where on the end is fine: that will get built into the composed query.

You can verify this very simply by monitoring the SQL connection; you'll see that it includes the where clause in the TSQL, and filters at the SQL server.

This allows for some interesting scenarios, for example a flexible search:

IQueryable<Customer> query = db.Customers;
if(name != null) query = query.Where(x => x.Name == name);
if(region != null) query = query.Where(x => x.Region == region);
...
if(dob != null) query = query.Where(x => x.DoB == dob);
var results = query.Take(50).ToList();

In terms of your assumptions, they are incorrect - it is really:

1. build composable query, composing (separately) from, join, join, select
2. further compose the query, adding a where (no different to the above compositions)
3. at some point later, iterate the query
   1. generate sql from the fully-composed query
   2. retreive rows
   3. map into model
   4. yield the results

note that the sql generation only happens when the query is iterated; until then you can keep composing it all day long. It doesn't touch the SQL server until it is iterated.

这篇关于检索数据LINQ2SQL点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！