CODEWITHSUNDEEP
c#asp.net-mvclinqentity-framework
Patrick
Patrick

Reputation: 2781

Get data in a Many-to-many relashionship

I have three Entities:

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.

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:

EDIT:

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));

Example:

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.

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);

Any idea?

Thanks.

Upvotes: 3

Views: 382

Answers (4)

VIPUL PARMAR
VIPUL PARMAR

Reputation: 292

I think you should try to use inner join that is also used to get data from many tables

Select TABLE1.Field1,TABLE1.Field2,TABLE2.Field1 from TABLE1 INNER JOIN TABLE2 on TABLE1.Field1=TABLE2.Field1

Upvotes: 0

Zaphod
Zaphod

Reputation: 1482

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;
            }

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

--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)));

Upvotes: 3

William
William

Reputation: 443

It looks like there are a few questions now going on here, I'll focus on the keyword portion of the question:

If the splitKeywords just needs to INTERSECT with an address' keywords (original answer)

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));

If the splitKeywords must be a SUBSET of an address' keywords

Notice how I'm referencing your context's addresses directly and not going through your keywordSearchQuery anymore. It's simpler this way.

var addressIds = (
                  from a in addresses
                  where !splitKeywords.Except(a.Keywords.Select(kw => kw.Name)).Any()
                  select a.Id
                 )
                 .ToList();

I tested both of these on my end by linking to hard coded objects. The new subset query might not work in LINQ to Entities. Let me know!

Upvotes: 2

daniel
daniel

Reputation: 26

Ok, you need to use Aggregate and Intersect:

 var result = (from ks in keywordSearchQuery
               where splitKeywords.Contains(ks.Name)
               select ks.Keyword.Addresses.Select(a => a.Id))
              .Aggregate((l1, l2) => l1.Intersect(l2))

Upvotes: 0

Related Questions