SQL to LINQ to get all records that match all values

Question

How can I transform this query to linq c# code:

SELECT k1.Ad_Id FROM KeywordAdCategories k1, KeywordAdCategories k2
WHERE k1.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'ALFA')
AND k2.Keyword_Id = (SELECT Id FROM keywords WHERE name = '145')
AND k1.Ad_Id = k2.Ad_Id

The ideia is to build a dynamic query based in the number of keywords that can be N (in the example they are 2: ALFA and 145)

Thanks

EDIT:

KeywordAdCategory Model:

public class KeywordAdCategory
{
    [Key]
    [Column("Keyword_Id", Order = 0)]
    public int Keyword_Id { get; set; }

    [Key]
    [Column("Ad_Id", Order = 1)]
    public int Ad_Id { get; set; }

    [Key]
    [Column("Category_Id", Order = 2)]
    public int Category_Id { get; set; }
}

Keyword Model:

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

If I had 5 Keywords I would get:

SELECT k1.Ad_Id FROM KeywordAdCategories k1, KeywordAdCategories k2, KeywordAdCategories k3, KeywordAdCategories k4, KeywordAdCategories k5
WHERE k1.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'KEYWORD1')
AND k2.Keyword_Id = (SELECT Id FROM keywords WHERE name = 'KEYWORD2')
AND k1.Ad_Id = k2.Ad_Id
AND k3.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'KEYWORD3')
AND k2.Ad_Id = k3.Ad_Id
AND k4.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'KEYWORD4')
AND k3.Ad_Id = k4.Ad_Id
AND k5.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'KEYWORD5')
AND k4.Ad_Id = k5.Ad_Id

What do your C# classes look like? Also, could you give an example of what the SQL would look like if the number of keywords was 5? I don't really understand why the KeywordAdCategories table is used twice ... There might be a better way to write this SQL, which would lead to a better LINQ example. — Peter, Feb 06 '13 at 19:16
@ɹǝʇǝd I thought that the SQL could be rewritten better, also. However this SO question points out that the "cleaner" solution is less performant: http://stackoverflow.com/questions/1054299/sql-many-to-many-table-and-query — Justin Pihony, Feb 06 '13 at 19:39

score 1 · Answer 1 · answered Feb 06 '13 at 22:25

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.

Hi thanks! It's working! Any chance you could merge this code with this one: http://stackoverflow.com/questions/14324390/ ? — Patrick, Feb 06 '13 at 22:59

pwnyexpress · Answer 2 · 2013-02-06T21:53:23.987

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

edited Feb 06 '13 at 21:53

answered Feb 06 '13 at 19:18

pwnyexpress

1,016
7
14

Thanks but your answer is returning Ad_Id's that only contain one of the keywords, and the objective is to get the Ad_Id's that contain all keywords – Patrick Feb 06 '13 at 21:02
@Patrick, double check that keywordnames is being populated with all of the names you're looking for and that you're taking case sensitivity into account when doing string comparisons. Also, try the second example in my recent edit. – pwnyexpress Feb 06 '13 at 21:55
I did but even the second example still return all the Ads that have one keyword or the order, and not both – Patrick Feb 06 '13 at 22:27

score -1 · Answer 3 · answered Feb 06 '13 at 19:13

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

answered Feb 06 '13 at 19:13

Maritim

2,111
4
29
59

This does not take into account the `AND`. It would be more of an `OR` – Justin Pihony Feb 06 '13 at 19:18
1

It would be far more constructive to actually put that in a comment instead of just downvoting an answer. – Maritim Feb 06 '13 at 19:19
1

The answer is wrong, so I did both. That is the point of downvoting, to make sure people realize when an answer is wrong. If it is close, I dont downvote, but if it is plain wrong, then I do – Justin Pihony Feb 06 '13 at 19:20
My code is based on the information given. He has some sort of list of these keyword ids which he can loop through and run the method for every iteration. I wouldn't call it wrong, I'd call it an alternative approach. – Maritim Feb 06 '13 at 19:21

SQL to LINQ to get all records that match all values

3 Answers3