Good about ORM

1. Developer is free from building T-Sql on the database tier which is not their major area.
2. Rapid development
3. Strongly typed object to ensure data type match with C# type

Page life expectancy

Database data records stored in pages which is 8kb per page, when the data being query, the relevant pages will be load into db memory for futher proccess. and they will stay in the memory until the memory is full and need to clean up the staled pages and make the room for pages required for the new queries.

Cluster indexed scan vs seeking

cluster index *scan* mean reading all data top to bottom cluster index *seeking* is way better than scan cuz it does filter on index so dont filter at client look at client statodtic on client receive bytes from server to understand improvement on byte traffic reduction

explicit join better than nested linq. join is more tsql style. lambda expression is oo better than explicit join.

nested linq	explicit join	lambda expression

code first has the benefit of source agnostic. but choosing the right data type is also impotant for performance.

datatype auto generate by ef raise concern to cost and perf. nvarchar vs varchar. taking up different size of space  given they storing same amount data.

imagine how is that affect and cascade to replica for  HA. BI and bulk load jobs.

also it affects performance as there are extra spaces kept for the same record. remember db engine use page to cache data for plan execution. db type not being evaluated will result in high db memory usage.

data type is critical in a sense that even the same record is stored, with different data type. the record dump to the page can be high as 79m down to 51b (without other changes like adding index) . overstated data type will result in resource dramatically overused.

ef offers the capabiloty of altering the datatype and length when exec the code first code to deploy to db. annotation of maxlengh or required can be defined at field level at poco or in a builder context object via fluent API, .IsUniCode mandate the string field map to a varchar rather than nvarchar (for Unicode for cross culture) which is double in size when storing the same data.

lazy loading is like you watching the online movie, you can view the first 10 mins w/o downloading complete clip to get start.

it fit for use when user navigation is uncertained. lamda expression of 'skip' can privide some support. once lazy load is set in ef all reading of the ref table is return null.

eager loading in contrast is aim for the use case that consumer of the data collection having predefine routine to navigate the reference data sets.

ef allows you to add interceptor to change its default crud operation by overwrite e.g. the delete clause to soft delete rather than hard delete.

In summary, 3 type of potential issues need to be considered.

indexing. cluster index, pointer that sorting the teference the table record. non cluster, pointers to the clustered index which itself is sorted. each of them has the overhead and size increase when record inserted or deleted.

when ef cannot performas expected, back to use the good old sp.