CODEWITHSUNDEEP
c#sql-serverlinqjoin
Jason Goss
Jason Goss

Reputation: 37

Linq Join when second table will have multiple records

Trying to create a Linq query that can do multiple searches in one command instead of having multiple search result pages. It is working great when I am trying to find multiple records that have a subject (in my case CHIEF_COMPLAINT) and comments with a specific word. The problem is when I want to search with serial numbers.

There are two issues here. One is that multiple pieces of equipment can be attached to a specific ticket and also a single piece of equipment can be associated with multiple tickets. When I query the table used to associate equipment to tickets (VIEW_WT_EQUIP, using the view because it is where the serial number is seen) I potential get multiple results with the same Ticket_ID.

This is the query that I have right now, but it returns no results when I put in a serial number that I know is in the system.

var query = from a in db.VIEW_WT_HEADERs
                    join c in db.VIEW_WT_EQUIPs on a.TICKET_ID equals c.TICKET_NUMBER into c_group
                    from c2 in c_group
                    join b in db.WT_EVENTs on a.TICKET_ID equals b.TICKET_ID
                    where b.COMMENTS.Contains(input) || a.CHIEF_COMPLAINT.Contains(input) || c2.SERIAL_NUMBER.Contains(input)
                    orderby a.TICKET_ID descending
                    select new { a.TICKET_ID, a.ENTRY_DATE, a.CONTACT, a.CHIEF_COMPLAINT, a.STATUS };

I also tried a method where I used 2 linq queries and put all the ticket numbers from a serial number search into a list, but the second query didn't like that I was trying to compare an int array.

I think I am just going about this wrong. Join is probably not the right way to do this, but I don't know how to tell the main query to pull all the tickets associated with a piece of equipment.

Please let me know where I can clarify, because I know this explination is rough.

Upvotes: 0

Views: 1054

Answers (1)

Fabio
Fabio

Reputation: 11990

I would put this as comment instead of an answer, but I want to show you some code, so I had to choose "answer".

If you are using Linq to Entities, you probably have a relationship between the objects. It means that the join is not necessary. You should only use join when no navigation property is available.

I can't tell exactly what you should do, but here is some code the might be helpful:

var query = from a in db.VIEW_WT_HEADERs
            from b in a.WT_EVENTs
            from c in a.VIEW_WT_EQUIPs 
            where b.COMMENTS.Contains(input) || a.CHIEF_COMPLAINT.Contains(input) ...
            orderby a.TICKET_ID descending
            select new { a.TICKET_ID, a.ENTRY_DATE, a.CONTACT, a.CHIEF_COMPLAINT, a.STATUS };

you can also use let to store a sub-expression:

var query = from a in db.VIEW_WT_HEADERs
            from b in a.WT_EVENTs
            from c in a.VIEW_WT_EQUIPs 
            let x = c.FirstOrDefault()
            where b.COMMENTS.Contains(input) || a.CHIEF_COMPLAINT.Contains(input) || x.SomeProperty ....
            orderby a.TICKET_ID descending
            select new { a.TICKET_ID, a.ENTRY_DATE, a.CONTACT, a.CHIEF_COMPLAINT, a.STATUS };

Those are just example, maybe it helps!

Upvotes: 2

Related Questions