Linq fetch distinct

Question

I have the query:

var q = (
    from c in db.tblStoreRecommendations
    where
    itemIDsInCart.Contains(c.ItemID)
    && !itemIDsInCart.Contains(c.RecommendItemID)
    select c
);

It will return something along the lines of:

ID    ItemID    RecommendItemID    Message
------------------------------------------
1     25        3                  Msg here
2     26        3                  Something else
3     27        8                  Another message

I need the query to filter out results that have the same RecommendItemID, this should not appear in returned results more than once.

If two exist, it can use either (random selection would be best). So the returned results should omit record ID 1 or 2.

Can anyone show me how to do this please? Thanks!

Answer 1

One approach would be to use GroupBy and then select the first item from each group:

var q = (
    from c in db.tblStoreRecommendations
    where
        itemIDsInCart.Contains(c.ItemID)
     && !itemIDsInCart.Contains(c.RecommendItemID)
    select c
).GroupBy(c => c.RecommendItemID)
 .Select(g => g.First());

---

If you're using this to display a random review, I would recommend foisting this into the using code rather than the LINQ query, by omitting the First like so:

var q = (
    from c in db.tblStoreRecommendations
    where
        itemIDsInCart.Contains(c.ItemID)
     && !itemIDsInCart.Contains(c.RecommendItemID)
    select c
).GroupBy(c => c.RecommendItemID)
 .Select(g => g.ToArray());

var random = new Random();
foreach (var r in q)
{
    var rec = r[random.Next(r.Length)];
    // use your recommendation
}

Answer 2

I believe you can add a distinct comparison to the end of your linq expression, like:

var q = (
  from c in db.tblStoreRecommendations
  where
  itemIDsInCart.Contains(c.ItemID)
  && !itemIDsInCart.Contains(c.RecommendItemID)
  select c
)
.Distinct((x, y) => x.RecommendItemID == y.RecommendItemID);

---

Edit

I just realized I did this using an extension method that I wrote myself and typically keep handy... here is the code for it:

    /// <summary>
    /// Provides a way to get a distinct list of items based on a lambda comparison operator.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="source"></param>
    /// <param name="equalityComparer">A comparison function that returns true of the two items are considered equal.</param>
    /// <returns>The list of distinct items.</returns>
    public static IEnumerable<T> Distinct<T>(this IEnumerable<T> source, Func<T, T, bool> equalityComparer)
    {
        var distincts = new List<T>();
        foreach (var item in source)
        {
            var found = false;
            foreach (var d in distincts)
            {
                found = equalityComparer(item, d);
                if (found)
                    break;
            }

            if (!found)
            {
                distincts.Add(item);
                yield return item;
            }
        }
    }