[Solved] update linq query result

[Issam]

wow i am fighting with this for 2 days now !
 
i am displaying the result of this linq query in a wpf datagrid

var v = from p in _context.EXAMENS_VALEURs
join b in _context.EXAMENS_TYPES_VALEURs on p.EXV_ID_VALEUR_TYPE equals b.ID_EXAMEN_TYPE_VALEUR
join c in _context.EXAMENS_TYPES_GROUPEs on b.EXTV_ID_GROUPE equals c.ID_EXAMEN_TYPE_GROUPE
select new ValeursResult()
{
  ID = p.ID_EXAMEN_VALEUR,
  Designation = b.EXTV_DESIGNATION,  
  Groupe = c.EXTG_DESIGNATION,
  Valeur = p.EXV_VALEUR
};
((CollectionViewSource)this.FindResource("ExamensView")).Source = v.ToList();



 
 
the ValeursResult type is declared like this
 

    public class ValeursResult
        {
           public int ID { get; set; }
           public string Groupe { get; set; }
           public string Designation { get; set; }
           public string Valeur { get; set; }
        }

 
the datagrid id bound to the collectionviewdource with this

<DataGrid  x:Name="grd2" AutoGenerateColumns="True" 
     ItemsSource="{Binding Source={StaticResource ExamensView}}" />

 
 
now i can view the result in my datagrid, i can change the values in the cells ,
but the update is not done on the server with 

_context.Savechanges()

 
i have also tried this line of code just before calling savechanges

((ListCollectionView)((CollectionViewSource)this.FindResource("ExamensView")).View).CommitEdit();

but again the databse table is never updated
 

thanks for your help

[Boris Georgiev]

Hello Issam,

As I understood from the description of the problem, the ValeursResult class is not persistent i.e. it is not mapped to table, and you are using it to populate the WPF datagrid. That is why when you are making changes in the datagrid, the database is not updated. You can look at the ValeursResult class as a Database View, which is read-only. You can populate the values in the properties, but when you change them the values in the database could not be changed.

To workaround this issue I will recommend you to take a look at the WebAPI with WPF MVVM sample in Telerik OpenAccess SamplesKit. The datagrid there is mapped to one table - "Cars" but at the same time shows values from the other table - "Categories". This approach is implemented with a converter and you can find the source code in the "CategoryIDToNameConverter.cs" class. Also you can take a look at the "CreateEditViewModel.cs" where the form is bind again to the "Cars" table, but there is a combobox which is bind to "Categories" and the value is selected from this table.

I hope that helps.

Regards,
Boris Georgiev
Telerik

[Issam]

thanks boris to point me to the right path .

now from my understanding , for each row a new query is executed to get the foreign key value(via the converter), 
so if i have 3 forign keys in my table , 3 query will be executed per row to get the values , right?

i am wondering how it could affect performance, and if it's not better to use database views for readonly datasets

[Boris Georgiev]

Hi Issam,

It will depends on your implementation. In the sample application you looked, there is Categories repository which implements the singleton pattern and the entities are loaded only once with one query. Then the converter take the entities from the memory and didn't create and execute new queries for every row. This approach is applicable when the referenced table doesn't have too many entities and they could be loaded in the memory. 

Another approach is to use a Fetch Strategy and load the referenced entities in the same queue. You can refer to this articles how to use Fetch Strategies. Also you can take a look at the Context API sample in Telerik OpenAccess Samples Kit, there is test section to show how to use Fetch Strategies.

If you want only to show values(read-only) from different tables in a grid, the best performance you will have if you use a view. In this case the view will be considered as a table by OpenAccess and will be executed one queue which will load only the required columns. The column selection will be executed on the server and no additional entities will be loaded, then there will be no unnecessarily entities loaded in the memory.

I hope that helps.

Regards,
Boris Georgiev
Telerik

[Issam]

well actually it helps a lot !, 

really appreciate :)

thanks and good day or night