Linq and searches

ajj3085 posted on Thursday, March 27, 2008

Hi,

I'm trying to convert my existing search code to linq. I have a large criteria object, which many properties. Some of the properties are enum flags; so you can specify both Invoices and Rmas, or just Orders

I'd like to do something like this:

IQueryable<Document> query = from documents in db.Document;

query = query.Where( some condition );

But that doesn't seem to work.

The idea is that if some particular property has a value, it should be included as part of the filter.. so in addition to saying all documents that are Invoices or Rmas, they also have to been sold to someone with a first name of Bob.

Any ideas?

rasupit replied on Thursday, March 27, 2008

you can build the condition as string.
Take a look at Scott Guthrie blog regarding Dynamic LINQ

ajj3085 replied on Friday, March 28, 2008

I found a way a like a lot better; you can do the following:

var query =
    from documents in db.Document
    select document;s

if ( !string.IsEmptyOrNull( criteria.PartNumber ) ) {
    query = query.Where(
        d => d.LineItems.Any(
            li => li.PartNumber.StartsWith( criteria.PartNumber )
        )
    );
}

And so on. Seems to work nicely, and you keep all the strongly typed linq goodness.

Andy

ajj3085 replied on Monday, March 31, 2008

Opps.. this doesn't work all the way across tables as I'd expect:

if ( !string.IsNullOrEmpty( criteria.State ) ) {
query = query.Where(
d => d.Document.Contacts.Any(
dc => dc.Address.State.StateName.StartsWith(
criteria.State
)
)
);

}

The problem is that StateId for the address can be null, and linq
generates something like this:
query ... AND EXISTS( SELECT * FROM CONTACTS LEFT JOIN ADDRESS LEFT
JOIN STATE WHERE STATE LIKE 'pattern%' )

Since it's using left joins, and a CONTACTS record always exists (and
a contact ALWAYS has a related Address record), that always returns
true. How can I change my expression so that if there's no related
State, the Contact doesn't get returned (and thus the EXISTS in the
where is false).

Any ideas?

Thanks
Andy

ajj3085 replied on Monday, March 31, 2008

If anyone is interested, I found a solution, although I'm not sure it's the most elegent.

Basically I defined another query, and when querying the related records I modifed that query by tacking on .Where calls. I also use a boolean to track if I need that query at all. Then at the end, I use the subquery to futher filter the original query by using another .Where call.

ajj3085 replied on Wednesday, April 02, 2008

For some reason, Linq isn't working as I'd expected.

I have these queries:
                var query =
                    from documents in db.Document
                    select new {
                        Document = documents,
                        Subtotal = db.GetDocumentSubTotal( documents.DocumentId.Value ),
                        Tax = db.GetDocumentTaxTotal( documents.DocumentId.Value )
                    };
                var contacts =
                    from dContacts in db.DocumentContact
                    select dContacts;

contacts is used to further filter query. The problem is if I specify the FirstName in a Shipping DocumentContact (each documnet has 3 contacts, shipping, sold to and billing) must begin with a specified string, like this:

                        contacts = contacts.Where(
                            c => c.ContactType ==
                                DocumentContact.ContactTypeToDbType(
                                    DocumentContactType.SoldTo
                                )
                        );
                    contacts = contacts.Where(
                        d => d.Document.DocumentContacts.Any(
                            c => c.FirstName.StartsWith(
                                criteria.FirstName
                            )
                        )
                    );

And then filter query using contacts like this:

                    query = query.Where(
                        d => contacts.Any(
                            c => c.DocumentId == d.Document.DocumentId
                        )
                    );

The Sql gets spit out as:
SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS [ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest] AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].[TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].[DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS [CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS [ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].[ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].[DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].[CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC], [t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
FROM (
    SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].[DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId], [t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].[DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].[CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].[TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId], [t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].[CategoryId], [t0].[SubCategoryId], [t0].[ShipDate], CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId])) AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].[DocumentId])) AS [value2]
    FROM [dbo].[vDocument] AS [t0]
    ) AS [t1]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[vDocumentContact] AS [t2]
    WHERE ([t2].[DocumentId] = [t1].[DocumentId]) AND (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[vDocument] AS [t3], [dbo].[vDocumentContact] AS [t4]
        WHERE ([t4].[FirstName] LIKE @p0) AND ([t3].[DocumentId] = [t2].[DocumentContactId]) AND ([t4].[DocumentId] = [t3].[DocumentId])
        )) AND ([t2].[ContactType] = @p1)
    )
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
-- @p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]

The problem is the bolded part of the query... I have no idea why Linq tosses this in!

Any ideas?

dpetrancuri replied on Wednesday, April 02, 2008

Sheer speculation on my part. Is there any possibility you have a foreign key constraint set up between vDocumentContact.DocumentContactID and vDocument.DocumentID by 'accident' in your database?

Respectfully,

ajj3085 replied on Thursday, April 03, 2008

Doh.. yes, you're right.

I had this on DocumentContact
[Association( IsForeignKey = true, Storage = "document", OtherKey = "DocumentId" )]
public Document Document { get; set; }

It should have been:
[Association( IsForeignKey = true, Storage = "document", ThisKey = "DocumentId" )]
public Document Document { get; set; }

Thanks for getting me to look at that more closely!