替代使用实体框架在c＃中对数据库的查询中使用方法

i separated my application into a DAL, BL, UI.

我将我的应用程序分成DAL，BL，UI。

I used entity framework code first throw repositories to access the sql database.

我使用实体框架代码首先抛出存储库来访问sql数据库。

public class Person{
   ...
}    
public class PersonRepository{

   Create(...){...}
   Update(...){...}
   Delete(...){...}
   GetById(...){...}
   Query(...){...}

   ...

Now the thing is the BL i'm working on a method to get all the Persons who are leaving near an adress

现在问题是BL我正在研究一种方法来让所有离开地址的人离开

 public GetPersonsNear(string Address){
 ...
 }
 private bool AddressesAreClose(string address1, string address2)
 {
 ...
 }

the thing is linq does'nt let me use my method (in a query passed in the "Query" method of the repository)

事情是linq没有让我使用我的方法（在存储库的“查询”方法中传递的查询中）

 ...
 PersonRepository personRepository = new PersonRepository();
 var person = repository.Query(p => AddressAreClose(adress,p.Adress); 
 ...

therefor i needed to get All the elements of the table in a list using a simple foreach loop to make the tests and keeping only the relevant ones

因此我需要使用一个简单的foreach循环来获取表中的所有元素以进行测试并仅保留相关的

  ...
 PersonRepository personRepository = new PersonRepository();
 var persons = personRepository.GetAll;
 foreach(person in persons)
 {
    if(AdressAreClose(adress,person.adress))
    ...
  }

for now i populated the database with only a few elements to test it, but i'm not sure it would work very well with the far more greater number it will contain later specially with all the test i'm planing to add

现在我只用几个元素来填充数据库来测试它，但是我不确定它是否能够很好地使用它后面包含的更大的数字，特别是我正在计划添加的所有测试

isn't there a more clever way to do this ??? I'm open to anything

是不是有更聪明的方法来做到这一点???我对任何事都持开放态度

1 个解决方案

#1

Well first of all, you should use generics in your repository, even if it's constrained to Person. This way you can build pipes/filters off your queries to clean up your LINQ queries and facilitate reuse.

首先，您应该在存储库中使用泛型，即使它受限于Person。这样，您可以根据查询构建管道/过滤器，以清理LINQ查询并促进重用。

Of course, without seeing the full signature/implementation of your Query method, it's hard to tell. But either way, you need to return IEnumerable<Person> or IQueryable<Person> to make the following work.

当然，如果没有看到Query方法的完整签名/实现，很难说。但无论哪种方式，您都需要返回IEnumerable 或IQueryable 来进行以下工作。

So, you could turn AddressesAreClose into a pipe/filter, like this:

因此，您可以将AddressesAreClose转换为管道/过滤器，如下所示：

public static bool WhereAddressesAreClose(this IQueryable<Person> source, string address)
{
   return source.Where(/* your conditions */);
}

Then you can use it in your LINQ query:

然后您可以在LINQ查询中使用它：

var person = repository
   .Query() // Should be IQueryable<Person>
   .WhereAddressAreClose(adress);
   .ToList();

Depending on the size of your data and whether or not your implementing caching, you should limit the results on the server (database), not post-query with a foreach loop.

根据数据的大小以及是否实现缓存，您应该限制服务器（数据库）上的结果，而不是使用foreach循环进行后查询。

If the performance isn't great, consider adding indexes, using compiled queries or moving to a stored procedure.

如果性能不佳，请考虑使用编译查询或移动到存储过程来添加索引。

#1