CODEWITHSUNDEEP
c#linqvisual-studio-lightswitch
Maarten
Maarten

Reputation: 49

How to populate an AutoCompleteBox filter with distinct values from one property?

My table has a Date field, from which I would like to query all distinct years, and use those years in my ACB screen filter for that same table.

I am trying to figure out the Linq code I need for this. I just need the query to return something like:

2012

2011

2010

and use these values as the Choice List for my Auto Complete Box.

Many thanks.

Upvotes: 1

Views: 2059

Answers (3)

Yann Duran
Yann Duran

Reputation: 3879

The only way that you can do what you want is by creating a custom RIA service, then adding it as a data source. It may seems daunting the first time, but it's really very easy.

This link will explain the basics. then you can use the LINQ syntax that Kyle showed in his answer.

How Do I: Display a Chart Built On Aggregated Data

Upvotes: 1

embedded.kyle
embedded.kyle

Reputation: 11466

You can't programmatically set the Choice List of an AutoCompleteBox. See this SO question.

However you can use LINQ in the _PreprocessQuery method. Create an empty query using Query Designer, click the down arrow next to "Write Code" and choose the _PreprocessQuery method. Then use @xeondev's LINQ code like this:

partial void Query1_PreprocessQuery(ref IQueryable<TableName> query)
{
    query = (from row in query
             where row.DateField != null
             select row.DateField.Value.Year).Distinct().AsEnumerable().Select(e => e.ToString());
}

Upvotes: 0

Laszlo Boke
Laszlo Boke

Reputation: 1329

If your Date field never contains null, this query will do on EF:

    var years = (from row in ctx.YourTable
                 select row.DateField.Year).Distinct().AsEnumerable().Select(e => e.ToString());

This returns an IEnumerable< string > but add .ToList() or ToArray() at the end if it suits to you.

And for the completness, if your Date field is nullable, you should filter out null values:

    var years = (from row in ctx.YourTable
                 where row.DateField != null
                 select row.DateField.Value.Year).Distinct().AsEnumerable().Select(e => e.ToString());

Upvotes: 2

Related Questions