score:-1 

public static int GetAdId(string KeywordId)
{
    var dbContext = new MyDbContext();

    var result = (from k in in dbContext.KeywordAdCategories
                   where k.Keyword_Id == KeywordId
                   select k.Ad_Id).SingleOrDefault();

    return result;
}

Run all your keyword ids through this method and you will get the corresponding ad id. I recommend that you read up on LINQ. It is a fantastic tool once you get to know it, and it's not that hard to learn, although it might seem a bit scary at first. Pluralsight.com have a lot of great videos that are easy to grasp and learn from :)

score:0 

List<string> keywordnames = new List<string>();
// populate keywordsnames list    

IQueryable<int> AdIds = from k in KeywordAdCategories
    where 
        (from kw in Keywords where keywordnames.Contains(kw.name) select kw.id)
        .Contains(k.keyword_id)
    select k.Ad_Id;

or

List<string> keywordnames = new List<string>();
// populate keywordsnames list   

List<int> keywordnameids = (from kw in ctx.Keywords
                            where keywordnames.Contains(kw.name)
                            select kw.id).ToList();

List<int> AdIds = (from k in ctx.KeywordAdCategories
                   where keywordnameids.Contains(k.Keyword_Id)
                   select k.Ad_Id).ToList();

score:1

This was quite a bit trickier that I thought when first looking at it, so I am probably over thinking it. But here is a solution that I believe will work for you:

var keywordList = new List<string>();
keywordList.Add("ALFA");
keywordList.Add("145");

var results = KeywordAdCategories.Select (kac => kac.Ad_Id).Distinct()
            .Select (a => 
                new
                { 
                    AdId=a,
                    Keywords=KeywordAdCategories.Where(kac => kac.Ad_Id == a).Select(kac => kac.Keyword_Id)
                })
            .Where(ac => ac.Keywords.Intersect(Keywords.Where(kw => keywordList.Contains(kw.Name)).Select (kw => kw.Id)).Count() == keywordList.Count())
            .Select (ac => ac.AdId);

Now to explain what I did here. First I wanted to start off with all the possible Ad id's so we have this line:

KeywordAdCategories.Select (kac => kac.Ad_Id).Distinct()

Of course this could probably be more easily retrieved from a distinct select from your ad table, but I am trying to only work with what you defined.

Next I select each ad id with its related collection of keywords into a list of anonymous typed objects:

.Select (a => 
              new
              { 
                  AdId=a,
                  Keywords=KeywordAdCategories.Where(kac => kac.Ad_Id == a).Select(kac => kac.Keyword_Id)
              })

Next I filter the results to only the objects that contain a collection of Keywords that intersects with our keyword list and has the same number of resulting elements as our keyword list.

.Where(ac => ac.Keywords.Intersect(Keywords.Where(kw => keywordList.Contains(kw.Name)).Select (kw => kw.Id)).Count() == keywordList.Count())

and finally I select out just the ad id's that result

.Select (ac => ac.AdId);

here is the emmited sql if you are intersted

-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'ALFA'
DECLARE @p1 NVarChar(1000) = '145'
DECLARE @p2 Int = 2
-- EndRegion
SELECT [t1].[Ad_Id]
FROM (
    SELECT DISTINCT [t0].[Ad_Id]
    FROM [KeywordAdCategories] AS [t0]
    ) AS [t1]
WHERE ((
    SELECT COUNT(*)
    FROM (
        SELECT DISTINCT [t2].[Keyword_Id]
        FROM [KeywordAdCategories] AS [t2]
        WHERE (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [Keywords] AS [t3]
            WHERE ([t2].[Keyword_Id] = [t3].[Id]) AND ([t3].[Name] IN (@p0, @p1))
            )) AND ([t2].[Ad_Id] = [t1].[Ad_Id])
        ) AS [t4]
    )) = @p2

I am sure that there are much more elegant ways to accomplish what you are wanting to do, but I wanted to see if I could get it all to happen in a single linq query. Hope this helps.


Related Articles