LET functionality in LINQ with ORM

[jon]

I am the using 2009 Q2 ORM DLLs and I am needing to do a subquery to grab the Male and Female StrainIDs for each cage.  This code looks good now, so hopefully it comes through without lots of linebreaks added to it.  Any idea why I am getting a "not supported" error when I run this query.
       var result = from c in ext
                 let StrainIdMale = scope.Extent<Animal>().Where(a => a.IdCage1 == c.IdCage).SingleOrDefault().Strain.StrainId
                    select new
                    {
                        c.IdCage,
                        Location = c.Location.Building + c.Location.Room,
                        StrainIdMale
                    };

Is LET not supported yet?  If not, please recommend a work-around.  Thanks.

[Zoran]

Hello jon,

Sub selects are currently not supported by Telerik OpenAccess ORM. They are planned to be delivered later this year. I think there should be a workaround if there are references connecting the Animal class and the class you originally query from. If you give us more detail about your model we might be able to give you a workaround query.

All the best,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[jon]

Okay.  Well, in this case we have an Animal table and a Cages table.  Unfortunately we have to allow animals to move between multiple cages at a time, so an animal can be in multiple cages, and multiple animals can be in a cage.   Not very intuitive, but these are our requirements.  Rather than setting up a many-to-many relationship, I found that an animal would not rotate between more than 5 cages at a time, so to the animals table I added IdCage1, IdCage2, ..., IdCage5 to be able to track things in a little finer detail and this way hopefully have a 1-to-many relationship.  So we set Animal.IdCage1 as a foreign key to the Cages table which has a Cages.IdCage column.

Now what I am trying to do is create a flat (1-layer) report of all cages and also include data that is related to the animals table (such as the animal pedigree or strain) in the report.  So the columns we need are:  Cage.IdCage, Cage.CageType, Cage.DateTimeSetup, Animal.Pedigree.PedigreeName.

I thought this would do it, but LET is not supported:

var

 

ext = scope.Extent<Cage>();
var result = from c in ext
   let StrainIdMale = scope.Extent<Animal>().Where(a => a.IdCage1 == c.IdCage).SingleOrDefault().Strain.StrainId
   where c.IsActive == true

Suggestions?

 

 

 

 

 

[jon]

Adding this statement into the Select New {} did not work either:

select new {
  StrainIdMale = (from a in scope.Extent<Animal>().Where(a => a.IdCage1 == c.IdCage) select a.Strain.StrainName)
};

Do to this error:
Exception Details: System.NotSupportedException: Execution of 'Telerik.OpenAccess.Query.ExtensionMethods:Select(IObjectScopeQuery`1,Expression`1)' on the database server side currently not implemented.

[Zoran]

Hi jon,

In each of the ways this Linq query could be written, that leads to an expression tree containing a sub-select node. These kind of expression are currently not processed in the appropriate manner with OpenAccess. This functionality is in phase of development and will take place in one of our service packs soon. We will notify you when it is available.

All the best,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.