SQLITE: Return 5 most recent records today and format date/time

I have a Xamarin.Forms app which has a ListView. I would like to populate the ListView with the 5 most recent items where the date is today’s date. If there are less than 5 items, the ListView should only display a row for each item (for example, if there is only one item, the ListView should only have one row).

I would then like to convert the date format to mm/dd/yyyy (as opposed to dd/mm/yyyy) and to change the time to 12-hour format. Is this possible?

public Task<List<Product>> GetProductAsync()
{
     DateTime todayDateTime = DateTime.Today;
     return _database.Table<Product>().OrderByDescending(x => x.ProductDateTime).Where(x.ProductDateTime.Date == DateTime.Today).Take(5).ToListAsync();
}

<ListView x:Name="recentProductList" SeparatorVisibility="None"
Grid.Row="1" Margin="20,0" VerticalOptions="FillAndExpand" HorizontalOptions="FillAndExpand">
            <ListView.ItemTemplate>
                <DataTemplate >
                    <ViewCell>
                        <Frame BackgroundColor="White" BorderColor="#F0F0F0" Padding="5" Margin="0,0,0,5" HasShadow="False">
                            <Grid HeightRequest="50" HorizontalOptions="FillAndExpand" VerticalOptions="Start">
                                <Grid.ColumnDefinitions>
                                    <ColumnDefinition Width="*"/>
                                    <ColumnDefinition Width="Auto"/>
                                    <ColumnDefinition Width="*"/>
                                </Grid.ColumnDefinitions>
                                <Label Text="{Binding ProductDateTime}" TextColor="#757575" FontSize="12" VerticalOptions="Center" Margin="20,0"/>
                            </Grid>
                        </Frame>
                    </ViewCell>
                </DataTemplate>
            </ListView.ItemTemplate>
</ListView>

protected override async void OnAppearing()
{
     base.OnAppearing();
     recentProductList.ItemsSource = await App.Database.GetProductAsync();
}

The issue I have is: The name 'x' does not exist in the current context for this part of the statement: Where(x.ProductDateTime.Date == DateTime.Today). I am unable to retrieve the most recent records.

How can I retrieve the 5 most recent records with today’s date – and then format the result (in mm/dd/yyyy and 12-hour format)?

Answer

modify your query like this

return _database.Table<Product>().OrderByDescending(x => x.ProductDateTime)
       .Where(y => y.ProductDateTime.Date == DateTime.Today).Take(5).ToListAsync();

to change the date format use a StringFormat

<Label Text="{Binding ProductDateTime, StringFormat='{0:MM/dd/yyyy}'}" TextColor="#757575" FontSize="12" VerticalOptions="Center" Margin="20,0"/>

see here for a list of available date format strings supported in .NET