How to approach querying a parent table based on values in a child table and a child tables related data table?

Question

Context: I have a parent table (Product), a child table (Transactions), and a child data table (RelatedData).

A Product has many transactions. Transactions of type 'A' have a RelatedData entry with SpecialData.

I would like to get all Product records that have their LAST transaction of type 'A' with the SpecialData value of 'B'

I'm using linq to SQL on a MSSQL database.

var data = from p in db.Product
           select p;

//various filters
    data = data.Where(...);

What I have right now for the query I want to implement:

data = data.Where(p=> p.Transactions
                            .Where(t => p.ProductID == t.ProductID && 
                                        t.TransType == 'A') 
                            .OrderByDescending(t => t.DateTime)
                            .FirstOrDefault(t => t.RelatedData //Get latest
                                                     .Single().SpecialData == 'B')
                        != null
                    );

It isn't returning any of the test data I've got set up in the database. Please help.

Well, have you tried simplifying any of the queries and debugging it? find out where and what data is returned and go from there? — trailmax, Feb 23 '18 at 16:17
Split it into several queries and check on which step you are filtering out what shouldn't be filtered out — OlegI, Feb 23 '18 at 16:18
If it's too complex in LINQ, try writing a Stored Procedure to get the desired results. — SS_DBA, Feb 23 '18 at 16:24
@RyanWilson ORMs don't need joins, *they* generate the join statements from the entity relations. The entities are related through the `Transactions` collection. Which means `p.ProductID == t.ProductID` is pointless btw — Panagiotis Kanavos, Feb 23 '18 at 16:45
@PanagiotisKanavos Interesting how that works, thanks for the info. I don't use ORMs as I prefer doing the database design myself. Along with the Stored Procs, Views, etc. — Ryan Wilson, Feb 23 '18 at 16:47
@DaltonRussel please provide the classes, table schema and test data. Don't force people to write everything from scratch in order to test the code. — Panagiotis Kanavos, Feb 23 '18 at 16:47
@RyanWilson ORMs aren't related to database design either, they with map *objects* to relational tables. Hence the acronym Object Relational Mapping. You can map objects to views, which is probably easier in this case - a ROW_NUMBER() would return the last entry immediatelly while there's no way to specify this in LINQ — Panagiotis Kanavos, Feb 23 '18 at 16:49
@PanagiotisKanavos Good point. Seems I need to read up on ORMs, I just don't see how using an ORM and LINQ is easier than just writing a stored procedure that could do what the person is asking for. — Ryan Wilson, Feb 23 '18 at 16:54
One way to debug is to put the profiler against the database and see the query issued against the database. Check [this question](https://stackoverflow.com/questions/4899974/how-to-view-linq-generated-sql-statements) for more details. — Alexei - check Codidact, Feb 23 '18 at 19:10

score 1 · Accepted Answer · answered Feb 23 '18 at 16:48

Your goal is:

I would like to get all Product records that have their LAST transaction of type 'A' with the SpecialData value of 'B'

From your requirement I understand that the key is to find the last transaction of each product.

Assuming your entities are like this:

public class Product
    {
        public virtual ICollection<Transaction> Transactions { get; set; }
    }

    public class Transaction
    {
        public int TransId { get; set; }
        public int ProductId { get; set; }
        public Product Product { get; set; }

        public string TransType { get; set; }
        public DateTime DateTime { get; set; }

        public virtual RelatedData RelatedData  { get;set;}
    }

    public class RelatedData
    {
        public int TransId { get; set; }
        public virtual Transaction Transaction { get; set; }
        public string SpecialData { get; set; }

    }

between Transaction and Related data is one to one relation.

Than you can do it like this:

  //last transaction for each product
            var transactions = db.Transactions.GroupBy(x =>x.Product)
                .SelectMany(a =>a.Where(b=>b.DateTime==a.Max(c=>c.DateTime)));
            //last transaction of TransType A
            var transA = transactions.Where(x => x.TransType == "A");

        //where SpecialData is B

        var transSpecialDataB = transA.Where(x => x.RelatedData.SpecialData == "B");

        //now you need to get your products
        var products = transSpecialDataB.Select(x => x.Product).ToList();

Hope this help

score 0 · Answer 2 · answered Feb 23 '18 at 16:39

Shot in the dark, without any data to test it out.

data = data.Where(p=> p.Transactions
                            .Any(t => t.TransType == 'A' && 
                                 t => t.RelatedData.FirstOrDefault().SpecialData == 'B') 
                 );

Notice that you don't need t => p.ProductID == t.ProductID because all Transactions should be related to that Product.

I am using FirstOrDefault() here because it seems like it could return more than 1 result. If you were to use Single or SingleOrDefault on a sequence with more than one element, it throws an exception.

How to approach querying a parent table based on values in a child table and a child tables related data table?

2 Answers2