Hi!

I tried to make some code, which takes any table/entity and any column/field in it and list all data in that column (i.e. into dropdown). It is very easy to make it in SQL

SELECT DISTINCT column FROM table;

but we have year 2009, why to make it this simple. Let's try LINQ. This is how I ended:

      LinqMetaData md = new LinqMetaData();

      var p = Expression.Parameter(Column.Table.EntityType, "row");

      IQueryable<string> q = 
           md.BookingWithAirports.Select<BookingWithAirportsEntity, string>(Expression.Lambda<Func<BookingWithAirportsEntity, string>>(Expression.Property(p, "CodeAirportArrival") , new ParameterExpression[] { p }));

(Column.Table.EntityType is the type of the entity = BookingWithAirportsEntity. CodeAirportArrival is the name of one column in this table)

The problem is, there are two BookingWithAirportsEntity left hardcoded, which I cannot get rid of. I think I could make another lambda for Select (though it is pretty difficult because it is generic method). Then I could use

table.GetQuery().Provider.CreateQuery(theWholeExpressionsPart);

Where table is MetaTable from DynamicData.

But all this is rather really difficult to tie together. I am a bit confused, because the SQL is so simple and other ways seems almost imposible.

So is this at least a good approach? (Lambda expressions)

BTW: How can I make it in plain LLBL? I mean I have the table, the column and want to fetch only distinct values from one column?

I suppose I will do SP for this, I am just too tired

BTW: How can I make it in plain LLBL? I mean I have the table, the column and want to fetch only distinct values from one column?

Are you speaking about executing raw SQL?

Or using Reflection and other techniques to instanciate ad entity or an entityField from it's corresponding name in the database, and hence fetch this entity/entityfiled?

Or are you asking about how to just fetch a single entityField?

Walaa wrote:

BTW: How can I make it in plain LLBL? I mean I have the table, the column and want to fetch only distinct values from one column?

Are you speaking about executing raw SQL?

Or using Reflection and other techniques to instanciate ad entity or an entityField from it's corresponding name in the database, and hence fetch this entity/entityfiled?

My second choice was to use classic LLBL. (I successfully made raw SQL query, but there are still some open problems (but it does not matter, I don't want to use raw SQL anyway)).

So I have EntityField field and type of entity (like UserEntity). (In fact I have MetaColumn from dynamic data.) And want a List<string> (or so) with values of that field from DB.

I didn't spent much time with classic LLBL approach (I preferred LINQ). I just tried using ExculdeIncludeField and it returned only one field, but not distinct (I think it is what it was supposed to do, because the result is still Collection not List/Array). Secondly I tried to use directly DAO, it looked better but I had not enough time to play with it.

I think I will go for classic LLBL, it looks more suitable for dynamic predicate construction than LINQ. In LINQ you must know what you want at compile time (as I see it), I just wanted to try LINQ. I will inform you about progress

Or are you asking about how to just fetch a single entityField?

in fact yes, but from collection which I know only by EntityType (i.e. MyProject.LLBL.EntityClasses.UserEntity)

So I have EntityField field and type of entity (like UserEntity). (In fact I have MetaColumn from dynamic data.) And want a List<string> (or so) with values of that field from DB.

I didn't spent much time with classic LLBL approach (I preferred LINQ). I just tried using ExculdeIncludeField and it returned only one field, but not distinct (I think it is what it was supposed to do, because the result is still Collection not List/Array). Secondly I tried to use directly DAO, it looked better but I had not enough time to play with it.

I think I will go for classic LLBL, it looks more suitable for dynamic predicate construction than LINQ. In LINQ you must know what you want at compile time (as I see it), I just wanted to try LINQ. I will inform you about progress

If you have the entityField already, then use a DynamicList as follows:

//Adapter code

DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(yourEntityField, 0);

DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, null, false);

The last parameter passed to the FetchTypedList method is allowDuplicates, setting it to false should genearte DISTINCT into the query.

Walaa wrote:

//Adapter code

DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(yourEntityField, 0);

DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, null, false);

But I am using SelfServicing ( and I like it). Thanks anyway. (I'll probably look into documentation again )

You should have said that earlier

// Self Service code:

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(yourEntityField, 0);

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, null, false, null, null, 0, 0);

aaa, I am second I tried fields.Add(yourEntityField) instead of fields.DefineField(yourEntityField, 0) for the first time But I found my mistake on second look to documentation.

Thanks again

I had it all the time "under my nose". Dynamic data contains sample of Autocomplete filter, which uses exactly what I was trying to do (in exactly same way) so it is possible

    private static String[] BuildFilterQuery(
        MetaTable table, 
        string prefixText, 
        int maxCount, 
        String columnName)
    {
        var column = table.GetColumn(columnName);

        // query = {Table(Customer)}
        var query = table.GetQuery();

        // row
        // entityParam = {row}
        var entityParam = Expression.Parameter(column.Table.EntityType, "row");

        // row.DisplayName
        var property = Expression.Property(entityParam, columnName);
        //property = {row.City}
        //var property = Expression.Property(entityParam, column.EntityTypeProperty);

        // row => row.Property
        // columnLambda = {row => row.City}
        var columnLambda = Expression.Lambda(property, entityParam);

        // "prefix"
        // constant = {"Lo"}
        var constant = Expression.Constant(prefixText);

        // row.DisplayName.StartsWith("prefix")
        // startsWithCall = {row.City.StartsWith("Lo")}
        var startsWithCall = Expression.Call(
            property,
            typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) }),
            constant);

        // row => row.DisplayName.StartsWith("prefix")
        // whereLambda = {row => row.City.StartsWith("Lo")}
        var whereLambda = Expression.Lambda(startsWithCall, entityParam);

        // Customers.Where(row => row.DisplayName.StartsWith("prefix"))
        // whereCall = {Table(Customer).Where(row => row.City.StartsWith("Lo"))}
        var whereCall = Expression.Call(
            typeof(Queryable),
            "Where",
            new Type[] { table.EntityType },
            query.Expression,
            whereLambda);

        // query.Select(row => row.Property)
        // selectCall = {Table(Customer).Where(row => row.City.StartsWith("Lo")).Select(row => row.City)}
        var selectCall = Expression.Call(
            typeof(Queryable),
            "Select",
            new Type[] { query.ElementType, columnLambda.Body.Type },
            whereCall,
            columnLambda);


     //query.Select(row => row.Property).Distinct
     //distinctCall = {Table(Customer).Where(row => row.City.StartsWith("Lo")).Select(row => row.City).Distinct()}
    var distinctCall = Expression.Call(
      typeof(Queryable),
      "Distinct",
      new Type[] { column.ColumnType },
      selectCall);


        // Customers.Where(row => row.DisplayName.StartsWith("prefix")).Take(20)
        // distinctCall = {Table(Customer).Where(row => row.City.StartsWith("Lo")).Select(row => row.City).Distinct().Take(20)}
        var takeCall = Expression.Call(
            typeof(Queryable),
            "Take",
            new Type[] { typeof(String) },
            distinctCall,
            Expression.Constant(maxCount));


        var result = query.Provider.CreateQuery(takeCall);

        List<String> list = new List<string>();
        foreach (var item in result)
        {
            list.Add(AutoCompleteExtender.CreateAutoCompleteItem(item.ToString(), item.ToString()));
        }

        return list.ToArray();
        //return query.Provider.CreateQuery(distinctCall);
    }

Definitely I don't like lambda expressions

Thanks for the feedback.