Get the difference between the following Linq objects for objects

Question

Summarizing the task in one line for clarity, as suggested:

I want to find all things that have different values ​​for any field and less than a week (based on another field)

---

Let's say I have a Users table and a User class.

Each User has the following fields:

• SignUpDate field with non-zero DateTime
• UserType field int, for this question let's say that it is either 1 or 0

I would like to select all pairs of users who have signed up for less than 7 days and have a different type of user.

My current (pathetic) attempt included OrderBy(r=>r.SignUpDate) and .ToList , which was not a big deal, as the number of users for every 2 weeks is small. (I grabbed all the users from this with overlapping weeks and compared the data).

However, I believe that my current solution is pretty bad. I have no idea how to approach this.

I think the main problem is that I don’t know how to solve the "select every two matching records" task in LINQ for Entities after ordering.

Any help is greatly appreciated, I am interested in how I can solve such a problem without asking my question in the future.

Input example

  SignUpDate UserType ------------------------------------ 2008-11-11 1 2008-11-12 0 2008-11-13 0 2008-12-13 0 2008-12-15 1 

Output example

Any meaningful way to indicate that the problematic pairs are:

  2008-11-11 1 2008-11-12 0 

(Different day and different type)

AND

  2008-12-13 0 2008-12-15 1 

(two different days and different types)

---

Here is the related SQL solution that I found.

Answer 1

I do not quite understand what problem you are trying to solve, so below are general recommendations. It sounds as if all two user registrations, which are “adjacent in time” and within a week apart, are the rule, but it sounds a bit strange.

Whenever you want to request a piece of information that is only available indirectly (i.e. something is not just a column value), use the projection to select the information needed to solve the problem.

 var query = from user in context.Users let previousUser = context.Users .Where( u => u.SignUpDate < user.SignUpDate ) .OrderBy( u => u.SignUpDate ) .FirstOrDefault() select new { User = user, PreviousUser = previousUser, IsDuplicate = previousUser != null && previousUser.UserType != user.UserType, SignUpDaysApart = user.SignUpDate.Subtract( previousUser.SignUpDate ) }; 

Once you have the data in a more accessible format, it becomes much easier to write a query to solve a business problem.

 var duplicates = (from d in query where d.IsDuplicate && d.SignUpDaysApart.TotalDays <= 7 select d).ToList(); 

Hope the above is an inspiration for you to come to a decision.

Answer 2

Came with sth like this

  private bool LessThan7DaysApart(DateTime d1, DateTime d2) { return (d1 - d2).Duration() < new TimeSpan(7, 0, 0, 0); } private void Match() { List<User> listfortype0 = users.Where(u => u.UserType == 0).ToList(); List<User> listfortype1 = users.Where(u => u.UserType == 1).ToList(); foreach (User u in listfortype0) { List<User> tmp = listfortype1.Where(u2 => LessThan7DaysApart(u2.SignUpDate, u2.SignUpDate)).ToList(); if (tmp.Count > 0) { List<User> matchedusers = new List<User> { u, tmp[0] }; listfortype1.Remove(tmp[0]); } } }