在许多一对多relashionship获取数据 [英] Get data in a Many-to-many relashionship

问题描述

我有三个实体：

public class KeywordSearch
{
    // Primary properties
    public int Id { get; set; }
    public string Name { get; set; }

    // Navigation properties
    public Keyword Keyword { get; set; }
}

public class Keyword
{
    // Primary properties
    public int Id { get; set; }
    public string Name { get; set; }

    // Navigation properties
    public virtual ICollection<Address> Addresses { get; set; }
}

public class Address
{
    // Primary properties
    public int Id { get; set; }
    public PTCouncil PTCouncil { get; set; }                 <---------- EDIT

    // Navigation properties
    public virtual ICollection<Keyword> Keywords { get; set; }
}

public class PTCouncil                                       <---------- EDIT
{
    // Primary properties
    public int Id { get; set; }
    public string Name { get; set; }
}

根据一组的话，我需要提取所有不同地址标识的。

Based on a set of words, I need to extract all the Distinct Address Id's.

这句话被搜索的KeywordSearch表，匹配的关键字，涉及到一个地址。

The words are searched in the KeywordSearch table, that match a Keyword, related to an address.

到目前为止，与威廉的帮助下，我有这个，但得到匹配所有和一些词来搜索关键字，我需要把他们都弄到：

So far, with the help of William, I have this, but get keywords that match all and some of the words to search, and I need to get them all:

编辑：

var addressIds = (
              from ks in keywordSearchQuery
              where splitKeywords.Contains(ks.Name)
              select ks.Keyword.Addresses.Select(k => k.Id)
             )
             .ToList()
             .Aggregate((a, b) => a.Intersect(b));

例如：

KeywordSearch = {1,"RENAULT",1},{2,"MORAIS",2},{3,"SOARES",3},{4,"CENTRO",4}
Keyword       = {1,"Renault",{1,2}},{2,"Morais",{1}},{3,"Soares",{1}},{4,"Centro",{2}}
Address       = {1,"Renault Morais Soares",{1,2,3}},{2,"Renault Centro",{1,2}}

If I search "RENAULT MORAIS SOARES", I should get AddressId = 1
If I search "RENAULT CENTRO", I should get AddressId = 2
If I search "RENAULT", I should get AddressId = 1,2

Actual Search Problem: If I search "RENAULT XXXX", I get 1,2 and I should get nothing.

我还需要按位置进行过滤，我已经试过，但我得到指定的类型成员PTCouncil'不是在LINQ支持实体的错误

I also need to filter by location, I have tried this but I get an error "The specified type member 'PTCouncil' is not supported in LINQ to Entities"

keywordsAddressIds = from ks in keywordSearchQuery
                     where splitKeywords.Contains(ks.Name)
                     select ks.Keyword.Addresses.Where(p => p.Location.Distance(centerPoint) < radius * 1000).Select(a => a.Id);

任何想法？

感谢。

推荐答案

您需要做这两个操作我想。

You need to do this in two operations I think.

首先获得所有关键字地址：

First get all keywords addresses:

var result = from ks in keywordSearchQuery
           where splitKeywords.Contains(ks.Name)
           select ks).ToList().Aggregate((a, b) => a.Intersect(b));

再看看所有的关键字有一个结果，如果没有，不返回任何

then see if all keywords have a result, if not, don't return anything

if (splitKeywords.Any(s => !result.Any(t => t.Name.Contains(s))))
            {
                return null;
            }

这是伪code，但我认为你应该能够从这里找到答案。

This is pseudo code, but I think you should be able to figure it out from here.

- EDIT--
只是出于好奇，是不是已经在使用多个操作？这是prevent DB调用，除非所有的标准是fullfilled？
但是，当你做一个.ToList（）您填充从数据库数据列表（操作1），然后到一个聚合+相交（操作2？）。我想你可以用同样的方法添加第二个操作（code未测试） - >

--EDIT-- Just out of curiosity, aren't you already using more than one operation? Is it to prevent DB call unless all criteria is fullfilled? But when you do a .ToList() you populate the list with data from DB (operation 1) and then to an Aggregate + Intersect (operation 2?). I assume you could add the second operation in the same way (code not tested) ->

var result = from ks in keywordSearchQuery
               where splitKeywords.Contains(ks.Name)
               select ks).ToList().Aggregate((a, b) => a.Intersect(b)).Any(s => !result.Any(t => t.Name.Contains(s)));

这篇关于在许多一对多relashionship获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！