Hello:

version: 2.6.9.206

I need to have a database record for each year...

so something like this:

        var query = from numbers in new int[] { 2009, 2010, 2011, 2012, 2013 }

from securityUser in linqMetaData.SecurityUser select securityUser;

which works, but grabs the whole SecurityUser database for each 'numbers' in the array (as if it was in a for loop).

So, I'd like to switch the first two lines (to force the array to the server processing), which fails.

Alternatively, if I create a dbo.years table, it works either way. But that seems redundant.

Is there a way to create the set expression from an array so that it can convert to sql and be treated like a table?

Thanks, -Ian

p.s. exception: [InvalidCastException: Unable to cast object of type 'System.Linq.Expressions.ConstantExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression'.] SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.MemberAccessEvaluator. HandleLinqExpressionAsSetExpression(LinqExpressionAsSetExpression expressionToHandle) +55 SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler. HandleExpression(Expression expressionToHandle) +935 SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler. HandleGroupJoinExpression(GroupJoinExpression expressionToHandle) +84 SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.MemberAccessEvaluator. HandleGroupJoinExpression(GroupJoinExpression expressionToHandle) +350 SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler. HandleExpression(Expression expressionToHandle) +646 SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler. HandleJoinExpression(JoinExpression expressionToHandle) +41 SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler. HandleExpression(Expression expressionToHandle) +769 SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler. HandleAggregateExpression(AggregateExpression expressionToHandle) +41 SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers. GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +191 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression) +679 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +13 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +13 System.Linq.Queryable.Count(IQueryable`1 source) +211

Please post the final code that fails. Also, the approximate SQL code of which you want to do.

this works: var query = from numbers in new int[] { 2009, 2010, 2011, 2012, 2013 } from securityUser in linqMetaData.SecurityUser select securityUser;

but doing query.Count() retrieves the whole securityUsers table 5 times.

(something like) this works: var query = from numbers in linqMetaData.Years from securityUser in linqMetaData.SecurityUser where new int[] { 2009, 2010, 2011, 2012, 2013 }.Contains(numbers.year) select securityUser;

but that means creating a table for all numbers that I want to use.

this doesn't work, but appears to be what I want: var query = from securityUser in linqMetaData.SecurityUser from numbers in new int[] { 2009, 2010, 2011, 2012, 2013 } select securityUser;

the sql? good question...

select * from security_user cross join (select 2008 UNION ALL select 2009) as T(x)

What if I have a user defined function that returns a table (the unions in the above query), can I use a function mapping through linq2llblgen with a function that returns a table?

I need to have a database record for each year...

I'm not sure you need a Cross Join.

Would you please post the DDL sql of the SecurityUser table? Also please post a sample of the result set you want to get.

Indeed, if SecurityUser contains a year, you could use a Contains on the in-memory collection of years with the year field from Security user. So we definitely need the DDL SQL of that table/description of how the entity looks like.

Example, if SecurityUser has a year field, you can do: var query = from securityUser in linqMetaData.SecurityUser where new int[] { 2009, 2010, 2011, 2012, 2013 }.Contains(securityUser.YearField) select securityUser;

which will result in a WHERE IN ... query

Sorry that I was unclear.

I need to create a grid of projected costs for a given number of years and given inflation factors, and I am trying to avoid fetching the entire structure and duplicating it for each year in memory.

These projections will not and are not be stored in the db.

So.. to continue with with the securityUser example, securityUser contains UserName, SecurityUserId, Password, etc., but no year.

For the time being, we've created a table of Numbers (that I can add DateTime.Now.Year to get years) so that we can do a cross join between securityUsers and Numbers.

So what I want is: userName1, amount1, 2009 userName2, amount2, 2009 userName1, amount3, 2010 userName2, amount4, 2010

where securityUser has: userName1,... userName2,...

and Numbers has: 1 2

I will be doing a custom projection to a DTO that contains the entity and a year field.

speaking of which... is there anything wrong with:

var query = from su in this._linqMetaData.SecurityUsers select new { securityUser = this._linqMetaData.SecurityUsers.FirstOrDefault(su2 => su.SecurityUserId == su2.SecurityUserId), };

This seems like a hack to the rule that you cannot store or pass to functions range variables.

If there is a better way of accomplishing the cross join, I'm all ears.

Otherwise, is it possible to use dbFunctionCalls that return a table? The example in the doc returns a decimal.

Thanks again.

So what I want is: userName1, amount1, 2009 userName2, amount2, 2009 userName1, amount3, 2010 userName2, amount4, 2010

And for this specific example, how could UserName1 & amount1 be related to year 2009?

How would the decision be made that userName1, amount1 goes with 2009 While userName1, amount3 goes with 2010?

amountN ~ f(securityUser, year, inflationFactors)

so it is calculated in memory (as part of the projection, perhaps... just getting to that part now).

mmm there is a missing link here that I can't recognize. Shouldn't these calculation be carried out in your code and outside of the database.

If the database doesn't hold information about the year or the inflation rate, why are you trying to send this information to the database to carry complex calculations, rather than fetching the required data and execute these calculation in code, which should be better, performance wise.

Again, I apologize...

You are correct. I meant what I would be displaying to the user, not what I was fetching from the database. What I want from the database would be userName and year columns.

The cross join to the (redundant) numbers table seems to be working, though there is no 'top' in the sql trace. Considering the grid loads quickly and indicates that there are 32 million records, I assume it is paginating (somehow)

speaking of which... is there anything wrong with:

var query = from su in this._linqMetaData.SecurityUsers select new { securityUser = this._linqMetaData.SecurityUsers.FirstOrDefault(su2 => su.SecurityUserId == su2.SecurityUserId), };

This is a very bad idea. 150 records used 550MB of additional memory for aspnet_wp.exe

I'm kinda lost here.

What I want from the database would be userName and year columns.

Wouldn't this somhow qualifies Frans suggestion, please go back to the query he suggested in his last post.