Convert variable column name query to DLinq

[Andrus]

I use method below to populate multicolumn picklist from database.
Columns names are passed to this method as strings.

How to convert this code to Linq-SQL ?

Andrus.

BindingList<PickListEntity> GetPickList(
string displayMember, string dm2, string valueMember, char discriminator)
{

return GetEntityList(string.Format(
@"SELECT {0}||' '||{1} as DisplayMember,
{2} as ValueMember
FROM klassif
WHERE discriminator=:discriminator
ORDER BY UPPER({0})
", displayMember, dm2, valueMember ),
CreateParameter("discriminator", discriminator) );
}

class PickListEntity {
public string DisplayMember { get; set; }
public object ValueMember { get; set; }
}

GetEntityList() is simple method which uses ADO .NET DataReader to run query
and create entity list.
CreateParameter() creates ADO .NET command parameter.

 

[Nicholas Paldino [.NET/C# MVP]]

Andrus,

Well, the first question would be "why"? There is an old saying, "if it
isn't broken, don't fix it".

More than likely though, assuming that you have a valid datacontext for
the data source (this doesn't look like SQL, with the "||" and "=:" in it),
you would have to add the Table and Column attributes to the PickListEntity
class to help with the mapping from the data to the object.

 

[Andrus]

Well, the first question would be "why"?

1. I do'nt want to have 2 different data access layers (ADO. NET direct
commands and Linq) in my application.

2. This query forces me to use database table and column names.
I want to use entity type and column names.
Some of my entity classes have discriminators. Using direct sql requires
re-implementing this in sql.

There is an old saying, "if it isn't broken, don't fix it".

My appl is in prototype stage.
I want to implement it right from the beginning.

More than likely though, assuming that you have a valid datacontext for
the data source (this doesn't look like SQL, with the "||" and "=:" in
it),

This is standard SQL
|| is standard concatenation operator in SQL. You can replace it with + for
MSSQL
: is ADO parameter prefix in npgsql (@ is also OK).

you would have to add the Table and Column attributes to the
PickListEntity class to help with the mapping from the data to the object.

PickListEntity class is bound to different tables and columns at runtime.
To to create Table andColumn attributes at runtime and concatenate two
columns, I havent found such sample in DLinq.
I want to retrieve only those 2 columns from server, not all columns.
Column names are passed as strings to method.

Andrus.