Cast<Customer>() causes exception
Andrus
Northwind db = CreateDB();
IQueryable<Customer> gqueryable = (from c in db.Customers
select new { c.CustomerID, c.City }).Cast<Customer>();
but got exception in Cast<>()
Why Cast() is not working ? It should be easy for Cast() to create new
Customer and assign matching properties.
How to fix ?
Is it possible to write fixed Cast() function which allows this ?
Andrus.
Marc Gravell
> Customer and assign matching properties.
That isn't what cast does; cast is literally that: a /cast/, not a
conversion.
Why should it be able to create a Customer from two random properties
(id & city)?
More to the point; if you want the customer - /ask/ for the customer??
Can I take this back a level - what exactly are you trying to do? (not
"how", but "what"?)
It is, for example, possible to do a *projection* to set the named
properties into a new object - but it would be a bit pointless; you'd
do better to simply work with the Customer objcets to begin with, and
your code would become:
IQueryable<Customer> gqueryable = db.Customers;
(which doesn't look like a useful start...)
Marc
Andrus
>> Customer and assign matching properties.
>
> That isn't what cast does; cast is literally that: a /cast/, not a
> conversion.
> Why should it be able to create a Customer from two random properties
> (id & city)?
I can use Dynamic Linq library to retrieve only those columns from Customer
table from database.
I want to update some properties of corresponding customer object in
database.
> More to the point; if you want the customer - /ask/ for the customer??
I tried to ask the customer in Dynamic Linq Library using
Select( "new Customer ( CustomerID, City )") buto got error
since table name Customer is not allowed in this Select() method.
> Can I take this back a level - what exactly are you trying to do? (not
> "how", but "what"?)
I want to retrieve dynamic column list from customer table from database and
edit those columns in WinForms DataGridView:
IQueryable<Customer> custq =
db.Customers.Select("new(CustomerID,City)").Cast<Customer>();
Customer cust = custq.First();
cust.City="Tallinn";
db.Attach( cust, true );
db.SubmitChanges()
Andrus.
Marc Gravell
with:
IQueryable<Customer> gqueryable = (from c in db.Customers
select new Customer {CustomerID = c.CustomerID, City = c.City });
Worth a try...
Marc
Marc Gravell
"Explicit construction of entity type 'ConsoleApplication1.Customer'
in query is not allowed."
Just in case it works in DbLinq, you might want to try:
public static class QueryExt {
public static IQueryable<T> Select<T>(this IQueryable<T>
source, params string[] propertyNames)
where T : new()
{
if (source == null) throw new
ArgumentNullException("source");
if (propertyNames == null) throw new
ArgumentNullException("propertyNames");
Type type = typeof(T);
var sourceItem = Expression.Parameter(type, "t");
var newExpr =
Expression.New(type.GetConstructor(Type.EmptyTypes));
var bindings = propertyNames.Select<string,MemberBinding>(
name=>Expression.Bind(
type.GetProperty(name),
Expression.Property(sourceItem, name))
).ToArray();
return source.Select(Expression.Lambda<Func<T, T>>(
Expression.MemberInit(newExpr, bindings),
sourceItem));
}
}
Marc Gravell
generic tuple as an intermediary, and does the LINQ-to-SQL projection
into the tuple, then a LINQ-to-objects projection from the tuple back
into the type. I'm not saying it is perfect, but it works...
My comments (other chain) about the risk of data loss still stand...
and validation of a partial object might be, erm, interesting - but...
Anyways: double-projection version (not tidied or optimised etc)
follows;
Marc
public static class QueryExt {
public static IQueryable<T> Select<T>(this IQueryable<T>
source, params string[] propertyNames)
where T : new()
{
if (source == null) throw new
ArgumentNullException("source");
if (propertyNames == null) throw new
ArgumentNullException("propertyNames");
Type sourceType = typeof(T), tupleType =
typeof(Tuple<,,,,,,,,,>);
Type[] tupleArgs = tupleType.GetGenericArguments();
if (propertyNames.Length > tupleArgs.Length)
{
throw new NotSupportedException("Too many properties
selected; max " + tupleArgs.Length.ToString());
}
PropertyInfo[] sourceProps =
Array.ConvertAll(propertyNames, name => sourceType.GetProperty(name));
for(int i = 0; i < sourceProps.Length; i++) {
tupleArgs[i] = sourceProps[i].PropertyType;
}
for(int i = sourceProps.Length; i < tupleArgs.Length; i++)
{
tupleArgs[i] = typeof(byte); // use for any surplus
type-args
}
tupleType = tupleType.MakeGenericType(tupleArgs);
PropertyInfo[] tupleProps = new
PropertyInfo[sourceProps.Length];
for(int i = 0; i < tupleProps.Length; i++) {
tupleProps[i] = tupleType.GetProperty("Value" +
i.ToString());
}
ParameterExpression sourceItem =
Expression.Parameter(sourceType, "t");
MemberBinding[] bindings = new
MemberBinding[sourceProps.Length];
for (int i = 0; i < sourceProps.Length; i++)
{
bindings[i] = Expression.Bind(tupleProps[i],
Expression.Property(sourceItem, sourceProps[i]));
}
Expression body =
Expression.MemberInit(Expression.New(tupleType.GetConstructor(Type.EmptyTypes))
, bindings);
object result = typeof(QueryExt).GetMethod("SelectUnwrap",
BindingFlags.NonPublic | BindingFlags.Static).MakeGenericMethod(
typeof(T), tupleType).Invoke(null, new object[]
{source, body, sourceItem, sourceProps, tupleProps});
return (IQueryable<T>) result;
}
static IQueryable<T> SelectUnwrap<T, TTuple>(IQueryable<T>
source, Expression select, ParameterExpression itemParam,
PropertyInfo[] sourceProps, PropertyInfo[] tupleProps)
where T : new() where TTuple : new() {
var items = source.Select(Expression.Lambda<Func<T,
TTuple>>(select, itemParam)).AsEnumerable();
MemberBinding[] bindings = new
MemberBinding[sourceProps.Length];
ParameterExpression tupleItem =
Expression.Parameter(typeof(TTuple), "t");
for (int i = 0; i < sourceProps.Length; i++)
{
bindings[i] = Expression.Bind(sourceProps[i],
Expression.Property(tupleItem, tupleProps[i]));
}
Expression body =
Expression.MemberInit(Expression.New(typeof(T).GetConstructor(Type.EmptyTypes))
, bindings);
Func<TTuple,T> projection =
Expression.Lambda<Func<TTuple,T>>(body, tupleItem).Compile();
return items.Select(projection).AsQueryable();
}
}
sealed class Tuple<T0, T1, T2, T3, T4, T5, T6, T7, T8, T9>
{
public T0 Value0 { get; set; }
public T1 Value1 { get; set; }
public T2 Value2 { get; set; }
public T3 Value3 { get; set; }
public T4 Value4 { get; set; }
public T5 Value5 { get; set; }
public T6 Value6 { get; set; }
public T7 Value7 { get; set; }
public T8 Value8 { get; set; }
public T9 Value9 { get; set; }
// extend at will...
}
Andrus
> For info, it looks like LINQ-to-SQL doesn't like this:
>
> "Explicit construction of entity type 'ConsoleApplication1.Customer'
> in query is not allowed."
It is interesting then why Frans postes this workaround in paraller thread ?
Maybe LLblGen also supports this ?
> Just in case it works in DbLinq, you might want to try:
Thank you, Marc. This works in DbLinq.
You have also posted very good DynamicQueryExtensions class and
StartsWith() extension method.
Unlike MS Dynamic Linq Library which does not work with generic
IQueryable<T> type,
your DynamicQueryExtensions class works well with IQueryable<T>.
I think you are genius.
I know you and Jon don't like this but I think I must start to use it.
Havent found other good solution which allow user to retrieve and and edit
only selected columns at runtime. I don't plan to use stored procedures so I
expect this will work for me.
DbLinq driver needs to fixed to update only chnaged columns.
Should I go in this way and work on changing DbLinq driver to support this ?
Andrus.
Marc Gravell
Good to hear; it was certainly an intriguing little experiment ;-p
I'll be interested to see whether EF supports it... I also don't know
about LLblGen...
> Should I go in this way and work on changing DbLinq driver to support this ?
I can't comment on that; I don't know what the DbLinq code is like,
nor the policy on edits.
Just to note: LINQ-to-SQL does support (last time I looked) changed-
columns-only updates, so I'd hope that EF does too... of course, you'd
also need to find an EF provider for your db...
Andrus
>I made a version that works with LINQ-to-SQL; essentially it uses a
> generic tuple as an intermediary, and does the LINQ-to-SQL projection
> into the tuple, then a LINQ-to-objects projection from the tuple back
> into the type. I'm not saying it is perfect, but it works...
thank you.
I have up to 170 properties in some entities. This is deployed database,
re-factoring its structure is expensive.
So I should add 170 type parameters, 170 commas etc. manually so source
code.
This makes code ugly.
Should I try this to create portable Select() for Linq-SQL and DbLinq ?
I don't understand what expression tree this method exactly generates.
How to get linq code of sample expression tree created with this method?
> My comments (other chain) about the risk of data loss still stand...
Only changed properties are updated and stored procedures are not used.
How data loss can occur in this case ?
> and validation of a partial object might be, erm, interesting - but...
Busines entities contain base properties (customer id, name) which are
retrieved always and validated by core always.
Other properties are extension properties retrieved on demand. They allow
default values always. Default value is always valid and will not
participate in validation.
Non-default value validation is done in 3 levels:
1. During entry: Column data type (eq. ComboBox alows only fixed values,
decimal allows only numbers)
2. Before save: Custom validation script compiled at runtime and called from
entity partial method.
3. On save: Database server column validation expressions, constraints and
foreign key references.
Andrus.
Marc Gravell
> code.
consider the second sample just "for info".
I'd also only worry about hte maximum number of columns that you want
to be able to dynamically edit - probably closer to 30 than 170...
> I don't understand what expression tree this method exactly generates.
First it finds a TTuple, where TTuple is Tuple<T1,T2,...,Tn,
byte,...,byte> - i.e. the correct types for each of the properties you
have asked for, and byte for any spares...
then it is pretty-much (where "Foo" and "Bar" are examples of the
properties you asked for):
IEnumerable<TTuple> step1 = source.Select(row=>new TTuple
{Value1=row.Foo, Value2=row.Bar, ...}).AsEnumerable();
return step1.Select(tuple=>new Customer {Foo=tuple.Value1,
Bar=tuple.Value2, ...}).AsQueryable();
> Only changed properties are updated and stored procedures are not used.
Fine if the provider only issues UPDATE statements for the columns
that have changed; I don't assume this, and your "DbLinq driver needs
to fixed to update only chnaged columns." comment means it might not
always be true.
> (validation)
Sounds like you have already thought about this, so should be OK ;-p
Marc
Andrus
> First it finds a TTuple, where TTuple is Tuple<T1,T2,...,Tn,
>byte,...,byte> - i.e. the correct types for each of the properties you
>have asked for, and byte for any spares...
>then it is pretty-much (where "Foo" and "Bar" are examples of the
>properties you asked for):
> IEnumerable<TTuple> step1 = source.Select(row=>new TTuple
>{Value1=row.Foo, Value2=row.Bar, ...}).AsEnumerable();
> return step1.Select(tuple=>new Customer {Foo=tuple.Value1,
>Bar=tuple.Value2, ...}).AsQueryable();
I use Skip() and Take() methods with constructed query for paged data
access.
I noticed that Linq-SQL compatible (secod) method generates select
statement which returns *all*
rows from database like Skip() and Take() methods are not present.
Only first, Linq-SQL incompatible method generates correct select statement.
Andrus.
Marc Gravell
Marc
Andrus
> Perhaps Take() and Skip() before the Select() ?
no, they are after Select(). I use
IQueryable<T> Queryable;
public IList<T> SupplyPageOfData(int lowerPageBoundary, int rowsPerPage) {
IList<T> l = Queryable.Skip(lowerPageBoundary).Take(rowsPerPage).ToList();
return l;
}
Andrus.
Marc Gravell
[aside: if you use the first version, it should work fine either way]
Yes, I guessed that they are currently... but what I mean is that if
you tweak your query so that Take() and Skip() are applid *before* the
new Select(), then they will be composed correctly. For example
(Northwind):
var custs = ctx.Customers.Skip(40).Take(100).Select("CustomerID",
"ContactName").ToList();
uses the SQL below, which is correct (paged at the SQL, and only our 2
columns returned).
Marc
SELECT [t2].[CustomerID] AS [Value0], [t2].[ContactName] AS [Value1]
FROM (
SELECT [t1].[CustomerID], [t1].[ContactName], [t1].[ROW_NUMBER]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].
[CompanyName]
, [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].
[City], [t0].[Re
gion], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]) AS
[ROW_NUMB
ER], [t0].[CustomerID], [t0].[ContactName]
FROM [dbo].[Customers] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
) AS [t2]
ORDER BY [t2].[ROW_NUMBER]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [40]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [100]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.21022.8
Andrus
> Yes, I guessed that they are currently... but what I mean is that if
> you tweak your query so that Take() and Skip() are applid *before* the
> new Select(), then they will be composed correctly.
I want to generate order by and where clauses also.
Should double projection Select() be used always only as last method ?
Andrus.
Marc Gravell
*separate* query, that will be operating in LINQ-to-objects. My original
approach, on the other hand, doesn't do this, so you can cmopose in any
sequence.