Problem with LINQ query

Question

The following works fine:

                    (from e in db.EnquiryAreas
                            from w in db.WorkTypes
                            where
                            w.HumanId != null &&
                            w.SeoPriority > 0 &&
                            e.HumanId != null &&
                            e.SeoPriority > 0 &&
                            db.Enquiries.Where(f => 
                                f.WhereId == e.Id && 
                                f.WhatId == w.Id && 
                                f.EnquiryPublished != null && 
                                f.StatusId != EnquiryMethods.STATUS_INACTIVE &&
                                f.StatusId != EnquiryMethods.STATUS_REMOVED &&
                                f.StatusId != EnquiryMethods.STATUS_REJECTED &&
                                f.StatusId != EnquiryMethods.STATUS_ATTEND
                            ).Any()
                            select
                            new
                            {
                                EnquiryArea = e,
                                WorkType = w
                            });

But:

               (from e in db.EnquiryAreas
                            from w in db.WorkTypes
                            where
                            w.HumanId != null &&
                            w.SeoPriority > 0 &&
                            e.HumanId != null &&
                            e.SeoPriority > 0 &&
                            EnquiryMethods.BlockOnSite(db.Enquiries.Where(f => f.WhereId == e.Id && f.WhatId == w.Id)).Any()
                            select
                            new
                            {
                                EnquiryArea = e,
                                WorkType = w
                            });

+

   public static IQueryable<Enquiry> BlockOnSite(IQueryable<Enquiry> linq)
    {
        return linq.Where(e => 
            e.EnquiryPublished != null && 
            e.StatusId != STATUS_INACTIVE &&
            e.StatusId != STATUS_REMOVED &&
            e.StatusId != STATUS_REJECTED &&
            e.StatusId != STATUS_ATTEND
        );
    }

I get the following error:

base {System.SystemException}: {"Method 'System.Linq.IQueryable1[X.Enquiry] BlockOnSite(System.Linq.IQueryable1[X.Enquiry])' has no supported translation to SQL."}

Did you google the error message? It's pretty self descriptive. — Winston Smith, Dec 02 '09 at 09:58
Check out this related question http://stackoverflow.com/questions/332670/simple-linq-to-sql-has-no-supported-translation-to-sql . Bruno forgot to paste this when copying his answer. — Yannick Motton, Dec 02 '09 at 10:33
I understand the error message, but not how I can rewrite this so that it works with BlockOnSite type of function. — Niels Bosma, Dec 02 '09 at 10:58
@Niels : you can't. Linq to SQL will never be able to translate your BlockOnSite method call to SQL, because it is not a supported method — Thomas Levesque, Dec 02 '09 at 11:08

score 1 · Accepted Answer · answered Dec 02 '09 at 10:22

1

Linq to Sql only translates certain method calls to SQL, and yours (BlockOnSite) is not one of them. Hence the error. The fact that your method takes an IQueryable<T> and returns an IQueryable<T> doesn't make it special.

answered Dec 02 '09 at 10:22

bruno conde

47,767
15
98
117

score 1 · Answer 2 · answered Dec 02 '09 at 12:43

Ok I solved it using:

        IQueryable<Enquiry> visibleOnSite = EnquiryMethods.VisibleOnSite(db.Enquiries);

        var combinations = (from e in db.EnquiryAreas
                            from w in db.WorkTypes
                            where
                            w.HumanId != null &&
                            w.SeoPriority > 0 &&
                            e.HumanId != null &&
                            e.SeoPriority > 0 &&
                            visibleOnSite.Where(f => f.WhereId == e.Id && f.WhatId == w.Id).Any()
                            select
                            new
                            {
                                EnquiryArea = e,
                                WorkType = w
                            });

Problem with LINQ query

2 Answers2