SubSonic 3 Linq Join Problems

Question

using the linqtemplates, I tried getting the linq syntax close to what is in the docs

  var query = from c in db.CountyLookups
     join s in db.StateLookUps on
     c.StateLookupID equals
     s.StateLookupID
     where c.Name2 == countyName &&
     s.Abbr == stateAbbr
     select new
     {
      Latitude = c.Latitude,
      Longitude = c.Longitude
     };

  var result = query.SingleOrDefault();

but when .SingleOrDefault() is called, I get a yellow screen of darn that says:

System.NotSupportedException: The member 'StateLookupID' is not supported

the stack trace ends up at:

SubSonic.Linq.Structure.TSqlFormatter.VisitMemberAccess(MemberExpression m)

the StateLookupID column has underscores in the database and is a regular int pk/fk.

what am I doing wrong?

Answer 1

So apparently VisitMemberAccess has no idea what to do with an int, only string and datetime (starting on line 152 of SubSonic.Linq.Structure.TSqlFormatter). I don't know why this would be called on a join, since a join is usually between an int pk/fk (or guid if you like).

I ended up scrapping the linq query in favor of SubSonic.Query.Select. Here is my new code that works:

  var query = db.Select.From<CountyLookup>()
   .InnerJoin<StateLookUp>()
   .Where(CountyLookupTable.Name2Column)
   .IsEqualTo(countyName)
   .And(StateLookUpTable.AbbrColumn)
   .IsEqualTo(stateAbbr);

I then call ExecuteTypedList and map the results back to my model class. Works like buttah. Just wanted to use linq in this case.

Answer 2

I get this error when I've added properties to my models (the IsValid property as mentioned in ASP.Net MVC 1.0, thanks Rob). I've had this problem on and off for a bit, and I think I've got it nailed down to the query builder trying to build a query for something that should be done in code, not TSQL.

When it tries to generate the SQL, it descends down the path to generate the TSQL via VisitMemberAccess on a complex type (maybe a another model) but it only knows how to perform operations on datetimes and strings in VisitMemberAccess. I'm sorry if this is a bit incoherent, but I'm trying to get my head around it.

To get around this consider using something like LinqKit AsExpandable prior to any operation which will do the TSQL generation. I've tried this on a simple OrderBy which was going BANG and it appears to work but i have no idea yet what it will do to performance.

Answer 3

Actually I take that back I overcame my problem problem by doing

Stuff.All().Where(x=>x.Someid == id).ToArray() .AsQueryable() .Where(x=>x.SomeProp.SomeFlag == true);

It is crud, but it works.

Answer 4

This still appears to be a problem; namely in simple cases such as:

var list = from lang in db.Languages
                       join site in db.SiteConfigLanguages on
                       lang.Code equals site.LanguageCode
                       select lang;

This should evaluate to simple SQL (although pointless in this example):

SELECT Language.* FROM Language LEFT JOIN SiteConfigLanguage ON Language.Code = SiteConfigLanguage.LanguageCode;

It fails inside the same VisitMemberAccess function as (in this case) Language is not a recognisable declaring type (i.e. String or DateTime). It is very similar to the description @matware provided above however it sounds as though the "IsValid" member is pure C# code whereas in this case lang.Code is simply a reference to a column in the database.

I'm currently investigating workarounds as this is only a portion of the larger LINQ query which is failing for me; if I find anything I will post it here. Otherwise, any other solutions/workarounds to this problem known?

UPDATE: Ignore me here; this is simply due to me missing a simple line on the LINQ statement; you need to make sure that you use the "into" keyword to complete things!

i.e.

    var list = from lang in db.Languages
               join site in db.SiteConfigLanguages on
               lang.Code equals site.LanguageCode into sl
               from siteLang in sl.DefaultIfEmpty()
               select lang;

I've got another error mind you but at least this particular exception is solved. The next one looks a bit nastier unfortunately (inside System.Linq library).