SubSonic 3 Linq connection problems
using linqtemplates i tried to get the linq syntax next 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 curse screen that reads:
System.NotSupportedException: Member 'StateLookupID' not supported
the stack trace ends with:
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?
source to share
So obviously VisitMemberAccess has no idea what to do with int, only the string and datetime (starting at line 152 of SubSonic.Linq.Structure.TSqlFormatter). I don't know why this would be called to connect as the connection is usually between int pk / fk (or guid if you like).
I ended up ditching the linq query in favor of SubSonic.Query.Select. Here's my new code that works:
var query = db.Select.From<CountyLookup>()
.InnerJoin<StateLookUp>()
.Where(CountyLookupTable.Name2Column)
.IsEqualTo(countyName)
.And(StateLookUpTable.AbbrColumn)
.IsEqualTo(stateAbbr);
Then I call ExecuteTypedList and map the results to my model class. Works like a buttah. Just wanted to use linq in this case.
source to share
I am getting this error when I add properties to my models (IsValid property as mentioned in ASP.Net MVC 1.0, thanks Rob). I've had this problem several times and I think I nailed it to the query builder, trying to build a query for something that needs to be done in code, not TSQL.
When he tries to generate SQL, he goes down the path to generate TSQL via VisitMemberAccess on a complex type (possibly a different model), but he only knows how to do datetime and string operations in VisitMemberAccess. I'm sorry if this is a bit incoherent, but I'm trying to get around it.
To get around this one should use something like LinqKit AsExpandable before any operation that will generate TSQL. I tried this on a simple OrderBy that was going to BANG and it seems to work, but I don't know yet what it will do with performance.
source to share
This still seems 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 be evaluated with simple SQL (although it doesn't make sense in this example):
SELECT Language.* FROM Language LEFT JOIN SiteConfigLanguage ON Language.Code = SiteConfigLanguage.LanguageCode;
It exits from the same VisitMemberAccess function as (in this case). The language is not a recognized type of ad (i.e. String or DateTime). This is very similar to @matware's description above, however it sounds like the "IsValid" element is pure C # code, whereas in this case lang.Code is just a reference to a column in the database.
I am currently looking into workarounds as this is only part of a larger LINQ query that is failing for me; if i find anything i will post it here. Otherwise, are there any other solutions / workarounds for this problem known?
UPDATE: Ignore me here; it is simply because I missed a simple line in the LINQ statement; you need to make sure you use the "in" keyword to shutdown!
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 have a different error, but at least this particular exception is allowed. The next one looks a little more annoying (in the System.Linq library).
source to share