wpf, entity framework, sqlite and all that jazz

so i wanted to do something with wpf and of course data had to be involved. for this project i first played a bit with velocitydb, then some more with ravendb - both great nosql databases, but their licenses drove me away and to SQLite. yep, it is not a nosql db, but is embedded and well known and maintained (which proved priceless when trying to access it with EF6, but this may be the subject a different post).

so loading up vs2013 (and alternatively 'blend') i created my entities and added a datagrid with one of them, F5 and off we go! nowhere
i have some data in my table but it is not shown, wtf?

a quick look at the xaml i notice this: what are these and how do i make them display my data?

soon i understand (i'm new to wpf, mvvm, databinding etc) that it's gotta be something like this creating the entities context, a bit of linq to entities, assign to viewsource.source and...? still nothing!!
i hate to give up on something that should be easy and obvious, so giving it another try - scrap those views and assign data directly to the grid's source and it worked! open the champagne!
wait a second, why can't i add a new row???
now here is gets messy and includes a lot of hair pulling and stackoverflow.com scrolling...
i don't remember the numerous attempts to fix this, but the solution was...i cannot say elegant, but life-saving - wrap the results in an ObservableCollection! this bloody worked...by the way there is another quirk a forgot to mention: if in the xaml your columns are DataGridTemplateColumns - bad things would happen, so to say
better use the datagrid.columns struct and define manually all your columns beautiful...
btw (again) - note the Mode=TwoWay and especially UpdateSourceTrigger=PropertyChanged
without them you may loose couple of precious hours, waisted to gentle hair pulling and swearing..

ah...life is beautiful...let me just, edit some of the fields in the grid and reload the app to ensure changes were saved to db...
what?? where are my changes? everything is so beautifully binded together, what happened?
one of several ways to solve this is via where context is your entities holder.
but several days into this i realized there is a better way: use the grid's RowEditEnding event.
btw i spent maybe two or three days trying to find a way to add new rows from the datagrid to the underlying db. i got really desperate and miserable because there was no example or explanation of how to add new rows as in my case: wpf and entityframework.i searched and searched and searched...probably if i had gotten to read and learn about wpf and binding and EF from scratch, i would've done this earlier, but i guess sometimes even the harder road leads you to your goal....stupid me, anyway..

so how to add new rows from the datagrid back to db with entityframework? turns out the way i solved it didn't include any mistery: use the RowEditEnding, but without relying on any magical bindings - go straightforward:
are we adding a new row? add it to the source
are we updating an existing row? find it in the datasource and updated it, like this: a few important things to note:

• in the xaml make sure your column Binding definitions include UpdateSourceTrigger=PropertyChanged. otherwise you'll loose the info from the newly added column - all fields will be empty
• this new row data (here it is p of type razhodi) cannot simply be added to the datasource!!! it is similar, but is not actually a genuine `razhodi` entity instance - you have to create one from the context (context.razhodis.Create()) and assign the values in the real one, before adding to the datasource. the maddening issue here is that context.SaveChanges() will not complain in any way!!! it will be as if everything is fine and all data is saved to db, only it mysteriously IS NOT.
• another minor trick is to use FirstOrDefault which will return null if nothing is found in db, while SingleOrDefault will return null also, but if there are multiple records in the collection it will throw. so i thought it is cleaner to use the former.
• in my case i had to explicitly load the updated data back in the datagrid. there is probably a more correct way to do this, i suspect that somehow binding is involved in this :)

so this is it. how to add new rows to wpf datagrid with entityframework. it is probably VERY ugly to an experienced wpf programmer, i know. but i needed a quick hack to serve as a base on which the improve.
next stop - MVVM!