locked
Translate many:many filtering SQL query to linq lambada RRS feed

  • Question

  • User-270429056 posted

    Hello, 

    I'm trying to get this to work, but it does not. 

    CustomerIQ = CustomerIQ.Include(s => s.EngagementCustomers.Where(w => w.Engagement.ActivityDate > DateTime.Now.AddDays(-14))).ThenInclude(e => e.Engagement);

    If I were to write it in SQL I would do something like this:

    SELECT *
    FROM dbo.Customers as cust LEFT OUTER JOIN 
    ( SELECT eng.*, map.CustomerID
    FROM
    MappingEngagements as map
    LEFT JOIN 
    Engagements as eng ON eng.EngagementID = map.EngagementID
    WHERE eng.ActivityDate > DATEADD("day",-14,GETDATE())
    ) as custmap ON custmap.CustomerID = cust.CustomerID AND custmap.EngagementID IS NOT NULL
    

    How can I make the above SQL to work in linq lambada? 

    Essentially, I want to load engagements that are newer than 14 days, but I also want to keep the customers that do not have any of these engagements.

    Thank you!

    Thursday, September 17, 2020 8:54 AM

Answers

All replies

  • User585649674 posted

    How many customers in total do you have? You should, have some pagination.

    Left outer join can be done using linq, but not very good performance. https://stackoverflow.com/questions/3404975/left-outer-join-in-linq

    I would suggest to run separate queries.

    Get the list of customer who had engagement for last 14 days.

    Get the full list of customers.

    from the second list remove the customer which are present in first list. Then add the first list to second list.

    The better way is to create a "View" in SQL database. with the necessary query,

    Friday, September 18, 2020 5:43 AM
  • User-270429056 posted

    Hello and thank you for answering. 

    There are steps before this stage that filters the customers etc, so there would not be that many, If I was displaying it would also normally apply the pagination at a later stage since this is a iqueryable, however in this case I may load all of them all to memory because I need to do some additional processing, so loading all the engagements would be extremely heavy.  

    The idea is to load them to the model object, but I'm not sure how to accomplish this. 

    So essentially this is a simplified view of my model, since there is a many-to-many relationship I have a proxy class that binds them. The issue is to find a way to only load the engagements that happen in the last few days. I failed to see a way to do that, but I would think there must be a way to do this? 

    Splitting up the queries could work, do you have experience on how I can "fill" the collection from a separate query and only get the items specified? Thank you.

    Customer class: 

    string CustomerID

    ICollection<EngagementCustomer> EngagementCustomers

    EngagementCustomer class: 

    string CustomerID

    long EngagementID

    Engagement class: 

    long EngagementID

    DateTime ActivityDate

    Friday, September 18, 2020 6:58 AM
  • User-270429056 posted

    Hello, 
    I tried splitting them up and combining them later, but I am getting an error. It needs to have the same structure for me to use Union. So I figured I could use .Where(x => x.EngagementID == 0) So that it never loads any data, but keeps the structure. If I remove the highlighted part the query runs, so I am certain its caused by this, but I don't know why it does not work like that. 

    InvalidOperationException: Lambda expression used inside Include is not valid.

    var CustomerIQ1 = CustomerIQ.Include(s => s.EngagementCustomers.Where(x => x.EngagementID == 0)).ThenInclude(e => e.Engagement);
                    var CustomerIQ2 = CustomerIQ.Include(s => s.EngagementCustomers).ThenInclude(e => e.Engagement).Where(s => s.EngagementCustomers.Any(s => s.Engagement.ActivityDate > DateTime.Now.AddDays(-14)));
                    CustomerIQ = CustomerIQ1.Union(CustomerIQ2);

    Friday, September 18, 2020 7:34 AM
  • User1312693872 posted

    Hi,Erik1988

    Erik1988

    Essentially, I want to load engagements that are newer than 14 days, but I also want to keep the customers that do not have any of these engagements.

    If you want use a model to connect the other two models, I think you should change your models first, details are in my working demo:

    Model:

    public class Engagement
        {
            [Key]
            public long EngagementID { get; set; }
            public DateTime ActivityDate { get; set; }
            public ICollection<EngagementCustomer> EngagementCustomers { get; set; }
        }
    public class Customer
        {
            [Key]
            public string CustomerID { get; set; }
            public ICollection<EngagementCustomer> EngagementCustomers { get; set; }
    
        }
     public class EngagementCustomer
        {
            public string CustomerID { get; set; }
            public Customer Customer { get; set; }
            public long EngagementID { get; set; }
            public Engagement Engagement { get; set; }
        }

    And the LINQ is:

    var xd = (from c in _db.Customer
                          join ec in _db.EngagementCustomer on c.CustomerID equals ec.CustomerID into joinedT
                          from cc in joinedT.DefaultIfEmpty()
                          join e in _db.Engagement on cc.EngagementID equals e.EngagementID into joinedK
                          from ee in joinedK.DefaultIfEmpty()
                          select new
                          {
                              c.CustomerID,
                              ActivityDate = ee.ActivityDate == DateTime.MinValue ? null : ee.ActivityDate.ToString(),
                              EngagementID = ee.EngagementID == 0 ? null : ee.EngagementID.ToString(),
                              CustomerID2 = cc.CustomerID
    
                          }).ToList();

    DbContext:

    public DbSet<Customer> Customer{ get; set; }
            public DbSet<Engagement> Engagement { get; set; }
    
            public DbSet<EngagementCustomer> EngagementCustomer { get; set; }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
    
                modelBuilder.Entity<EngagementCustomer>()
                    .HasKey(c => new { c.CustomerID, c.EngagementID });
                modelBuilder.Entity<EngagementCustomer>()
                    .HasOne(bc => bc.Customer)
                    .WithMany(b => b.EngagementCustomers)
                    .HasForeignKey(bc => bc.CustomerID);
                modelBuilder.Entity<EngagementCustomer>()
                    .HasOne(bc => bc.Engagement)
                    .WithMany(c => c.EngagementCustomers)
                    .HasForeignKey(bc => bc.EngagementID);
            }

    Best Regards,

    Jerry Cai

    Friday, September 18, 2020 11:24 AM
  • User475983607 posted

    Sometimes writing RAW T-SQL is much easier and more efficient than LINQ.  I write raw queries often.  I also take advantage of stored procedure and views.    LINQ converts to SQL.  I don't see the need to always convert SQL to LINQ just to convert back to SQL.

    https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

    https://www.learnentityframeworkcore.com/raw-sql

      

    Friday, September 18, 2020 12:06 PM
  • User-270429056 posted

    Thank you for looking into this. 
    My model and context are the same as how you wrote it. And they are working.

    My query is different than yours, but I don't see how the one you posted filters out engagements that are older than 7 days. 


    I want to make sure I don't load all the engagements to memory and instead filter them out on the SQL server. The way I understanding it the select new is also not going to work well for me as I would need to update it every time a new field is added to the models. 

    I think it could work if I am able to load the included table without data and then use the union to merge them. But it seems like I'm not able to do that. 

    I have also tried this, but it also does not work:

    var CustomerIQ1 = CustomerIQ.Include(_ => new Collection<EngagementCustomer>()).ThenInclude(e => e.Engagement);
    var CustomerIQ2 = CustomerIQ.Include(s => s.EngagementCustomers).ThenInclude(e => e.Engagement).Where(s => s.EngagementCustomers.Any(s => s.Engagement.ActivityDate > DateTime.Now.AddDays(-7)));
    CustomerIQ = CustomerIQ1.Union(CustomerIQ2);

    Friday, September 18, 2020 2:44 PM
  • User-270429056 posted

    I solved my problem by using this:
    https://entityframework-plus.net/

    This allows you to use includefilter()

    Thanks for your help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 19, 2020 3:05 PM