0

Ok, I'm not understanding something here with what I am attempting to query here.

I have a list like the following.

Title   Subject                     Content                    Date          Status          User
IAM     Pending Items               Please go to this Link     7/22/2014     Unprocessed     abc@123.com
IAM     Pending Items               Please go to this Link     7/23/2014     Unprocessed     abc@123.com
IAM     Pending Items               Please go to this Link     7/22/2014     Unprocessed     abc@123.com
IAM     Pending Items               Please go to this Link     7/23/2014     Unprocessed     abc@123.com
IAM     Items Awaiting attention    Please go to this Link     7/22/2014     Unprocessed     abc@123.com
SAM     Pending Items               Please go to this Link     7/23/2014     Unprocessed     abc@123.com
SAM     Pending Items               Please go to this Link     7/22/2014     Unprocessed     xyz@123.com
SAM     Pending Items               Please go to this Link     7/23/2014     Unprocessed     xyz@123.com
SAM     Items Awaiting attention    Please go to this Link     7/22/2014     Unprocessed     xyz@123.com
IAM     Pending Items               Please go to this Link     7/23/2014     Unprocessed     xyz@123.com

Goal State Results:

Title   Subject                     Content                    Date          Status          User
IAM     Pending Items               Please go to this Link     7/23/2014     Unprocessed     abc@123.com
IAM     Items Awaiting attention    Please go to this Link     7/22/2014     Unprocessed     abc@123.com
SAM     Pending Items               Please go to this Link     7/22/2014     Unprocessed     xyz@123.com
SAM     Items Awaiting attention    Please go to this Link     7/22/2014     Unprocessed     xyz@123.com

It is the distinct result for the subject and user fields. I'm not sure how to make this happen with Linq. I have done simple queries before, but this seems beyond that.

My latest attempt is:

public override void Execute(Guid contentDbId)
    {
        //Get a reference to the current site collection's content database
        SPWebApplication webApplication = this.Parent as SPWebApplication;
        SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];

        //Get a reference to the "Tasks" list in the RootWeb of the first site collection in the content database
        SPList list = contentDb.Sites["sites/TT"].RootWeb.Lists["DailyEmailNotifications"];

        MyCustomEqualityComparer itemEqualityComparer = new MyCustomEqualityComparer();

        List<SPListItem> emailList = list.Items.Cast<SPListItem>()
                                     .Where(item => item["Status"].ToString() == "Unprocessed" && ((DateTime)item["SendDate"] == DateTime.Today || (DateTime)item["SendDate"] == DateTime.Today.AddDays(-1)))
                                     .Distinct(itemEqualityComparer)
                                     .ToList();
        //List<SPListItem> emailList = (from SPListItem item in list.Items
        //                              where item["Status"] == "Unprocessed" && ((DateTime)item["SendDate"] == DateTime.Today || (DateTime)item["SendDate"] == DateTime.Today.AddDays(-1))
        //                              select item).Distinct().ToList();
        //List<SPListItem> emailList = (from SPListItem item in list.Items
        //                              where item["Status"].ToString() == "Unprocessed" && ((DateTime)item["SendDate"] == DateTime.Today || (DateTime)item["SendDate"] == DateTime.Today.AddDays(-1))
        //                              group item by
        //                              {
        //                                  item["EmailSub"],
        //                                  item["EmailSendTo"]
        //                              } into gcs
        //                              select new List<SPListItem>()
        //                              {
        //                                    item["EmailSub"], emailGroups =
        //                              }).ToList();

        foreach (SPListItem item in emailList)
        {
            item["Status"] = "Send";
            item.Update();
        }
    }
1

In this case I would use a custom IEqualityComparer. First, you would need to make the equality comparer:

public class MyCustomEqualityComparer : IEqualityComparer<SPListItem>
{
    public bool Equals(SPListItem x, SPListItem y)
    {
        if (x["User"].ToString() == y["User"].ToString()
            && x["Subject"].ToString() == y["Subject"].ToString())
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public int GetHashCode(SPListItem obj)
    {
        return obj.UniqueId.GetHashCode();
    }
}

Then you can use the equality comparer in an overload of Distinct().

MyCustomEqualityComparer itemEqualityComparer = new MyCustomEqualityComparer();

List<SPListItem> myList = spList.Items.Cast<SPListItem>()
                                      .Where(item => item["Status"] == "Unprocessed")
                                      .Distinct(itemEqualityComparer)
                                      .ToList();

EDIT:

To walk through the logic flow, here is what I intended to happen with that bit of code:

spList.Items.Cast<SPListItem>()

First, we use Cast<T>() to cast the items in the IEnumerable SPListItemCollection into a generic IEnumerable<T> collection that the other LINQ methods can work on. We pass that on to:

.Where(item => item["Status"] == "Unprocessed")

Where() will take every item in the collection and pass it through the lambda expression provided, which is an anonymous function used for some simple conditional logic. All of the items that meet the condition in the lambda are then returned by the Where() method as another generic IEnumerable<T> collection which is then passed on to the next LINQ method:

.Distinct(itemEqualityComparer)

Distinct(), on it's own, will use the default equity comparer of the type in the collection passed to it to determine "distinctness" in the collection. That works fine for simple types like string or int. In this case, we have a more complex type, and we have more complex conditional logic that we want to use to evaluate equality. The overload for Distinct() allows us to do just that by encapsulating our custom conditional logic in a custom IEqualityComparer object. When we pass the custom equality comparer to Distinct(), it iterates over all the items in the collection, but uses the custom conditional logic to determine equality (or non-equality, and thus distinctness). It then returns yet another generic IEnumerable<T> collection on to the last line:

.ToList();

where we convert the IEnumerable<T> into a List<T>.

Now, what I honestly don't know all that much about is where the hash code comes into play. It may very well be that the way I originally suggested to implement the GetHashCode() method is what is tripping up the Distinct() method. I checked some other code where I have a custom equality comparer that works fine (and is doing a similar comparison -- checking for the equality of two properties to determine overall equality), and I implemented that slightly differently. If I adapt that to what you need to do, it would look like this:

public int GetHashCode(Office obj)
{
    return obj.UniqueId.ToString("N").GetHashCode();
}

So, maybe try changing the GetHashCode() implementation and see if that works.

In any case, what you have worked out for your answer is essentially what Distinct() with a custom equality comparer would be doing -- iterating over the entire collection, comparing everything using some custom comparison logic.

As far as what would be faster / more efficient, I really couldn't say.

  • Thanks for the response but I am still missing something I guess because it returns all results and only hits the GetHashCode function. Even hitting the Equals function I am not seeing how a one to one comparison is going to help here. – Christopher Jul 22 '14 at 12:03
  • What do you mean you don't see how a one to one comparison is going to help? How do you think Distinct() works behind the scenes? It's taking an IEnumerable collection, and going through each item, one by one, and comparing it to the others, to determine which ones are "distinct". Now, maybe the code I wrote to determine equality is wrong, but that is what I came up with based on what you said you were looking for: "distinct result for the subject and user fields." – Dylan Cristy Jul 22 '14 at 13:21
  • I did not see it doing that or see how that would happen. It did not seem to me that it was looking across the entire collection but a single item. I have taught myself .Net and I know I have much to learn. I just couldn't get that to work or walk through the logic flow. – Christopher Jul 22 '14 at 15:23
  • When you say it only hits the GetHashCode function, were you stepping through the code to see that? And when you say it seemed to you that it was not looking across the whole collection but only at a single item, was that because it only hit the GetHashCode method once? Or did it hit that more than once? I will update my answer to walk through the logic flow. – Dylan Cristy Jul 22 '14 at 20:51
  • Ok, your logic makes sense and thanks for that. I originally thought that that is where you were going with it but I wasn't fully thinking through the override. It hits the GetHashCode for every item returned in the list but I can't get it to hit the Equals function. When I get time I'm going to try again to get your code to work, for learning if nothing else and when/if I do I'll mark it as a answer. – Christopher Jul 24 '14 at 22:48
0

This is the answer I came up with, but it is not at the efficiency I was hoping for. I will up vote and mark as answer anything faster that someone can come up with that I can get working.

   List<SPListItem> emailList = (from SPListItem item in list.Items
            where item["Status"].ToString() == "Unprocessed" && ((DateTime)item["SendDate"] == DateTime.Today || (DateTime)item["SendDate"] == DateTime.Today.AddDays(-1))
            select item).ToList();

   emailList[0]["Status"] = "Send";
   emailList[0].Update();
   for (int i = 1; i < emailList.Count; i++)
   {
       for (int j = i-1; j >= 1; j--)
       {
          if (emailList[i]["SendToEmail"].ToString() == emailList[j]["SendToEmail"].ToString() && emailList[i]["EmailSub"].ToString() == emailList[j]["EmailSub"].ToString())
           {
               break;
           }
           if (j == 1)
           {
               emailList[i]["Status"] = "Send";
               emailList[i].Update();
           }
        }
   }
  • Are "SendToEmail" and "EmailSub" the internal names of the "Subject" and "User" fields in your original post? If so, try substituting those as the field names in the equity comparer, and see if that works. – Dylan Cristy Jul 22 '14 at 16:59
  • The names in the code are the actual field names. The titles in the sample data in the question were just nicer for an example. User = SendToEmail Subject = EmailSub I did make the proper substitutions in my code and it did not filter out the duplicate fields. – Christopher Jul 22 '14 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.