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

Question

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.

Laszlo Boke · Answer 1 · 2012-09-16T12:25:18.510

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());

score 1 · Accepted Answer · answered Sep 17 '12 at 13:46

1

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

answered Sep 17 '12 at 13:46

Yann Duran

3,842
1
24
24

Thanks Yann. I guess for the question if have, the solution you are suggesting is a bit too far fetched... Too complex for a high level developer who is used to simple SQL Server ;). I gave up after a couple of hours, not exactly understanding what I am doing... – Maarten Sep 17 '12 at 20:49
Yeah, it really depends on how necessary the query is. Are you saying that you found that article too hard to follow? I know it looks complicated, but it's really only two simple classes & one linked file. The hardest part is really what you do *in* the RIA service, not creating the service itself. You can also use a RIA service to execute a stored procedure (Eric has an article for that as well), but of course you still need to create a RIA service. – Yann Duran Sep 18 '12 at 00:22
Just need some more time to figure this out (I will). Maybe this specific query isn't that necessary, but I tend to use a lot of these self-populating drop down lists. Probably due to my pre-millenium Lotus Notes background... – Maarten Sep 18 '12 at 17:45
Yeah, you defiitely have to "relearn" how to do some things with LightSwitch, if you have experience with other programs. It does an incredibale amount of work for you, but it also requires some things to be done in a specific way. Once you get used to it, it'll just be second nature. – Yann Duran Sep 19 '12 at 00:32

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

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());
}

edited May 23 '17 at 12:12

Community

1
1

answered Sep 17 '12 at 12:36

embedded.kyle

10,976
5
37
56

Kyle, although your LINQ query is valid, that won't work in LightSwitch, you can't change the "shape" of a query's entity in the *PreProcessQuery* method, you can only restrict the records returned that it returns. – Yann Duran Sep 17 '12 at 13:43
@YannDuran I was merely commenting on _where_ the LINQ query should go. The actual query I took from xeondev and I didn't look closely at it. I see that you're right about changing the shape. But wouldn't changing the last line to just `select row).Distinct()` and then changing the Summary Property of the table to the Date column so that the date is what appears in the ACB, work? Or am I missing something there as well? – embedded.kyle Sep 17 '12 at 15:10
Kyle, thanks for your suggestion (I do get a single column just showing the years), but the query still returns all years (duplicates)... – Maarten Sep 17 '12 at 20:51
@Maarten Ahh...now I see what Yann sees. xeondev's query will get you the list that you want (i.e. a list of unique years). But you can't assign that to an ACB in normal code (I'm actually working on something similar and coming up short [see here](http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/25cc79c9-f091-48ea-8619-240ac977e3f4) ) and you can't set that to an ACB in `_PreprocessQuery` because of what Yann said above. My suggestion of `select.row).Distinct()` will return duplicates because of data in the other columns. I think Yann's right, an RIA Service may be it. – embedded.kyle Sep 17 '12 at 21:20
@Maarten You might possibly be able to achieve what you want using `DistinctBy` as mentioned by Jon Skeet in his answer to [this SO question](http://stackoverflow.com/questions/998066/linq-distinct-values). But I've never used it so I'm not sure. – embedded.kyle Sep 17 '12 at 21:22
From memory Distinct doesn't work for queries in LightSwitch, because the entire entity is returned in the query, so the ID alone will make the record unique. – Yann Duran Sep 18 '12 at 00:18

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

3 Answers3