EF Code First mapping non public properties

in the Contextclass add:

protected override void OnModelCreating(DbModelBuilder modelBuilder)



{

base.OnModelCreating(modelBuilder);

modelBuilder.Conventions.Add(new NonPublicColumnAttributeConvention());



}
and this is the convention




///

Convention to support binding private or protected properties to EF columns.


///

public sealed class NonPublicColumnAttributeConvention : Convention






{

public NonPublicColumnAttributeConvention()



{

Types().Having(NonPublicProperties)

.Configure((config, properties) =>



{

foreach (PropertyInfo prop in properties)



{

config.Property(prop);



}

});

}

private IEnumerable<PropertyInfo> NonPublicProperties(Type type)



{

var matchingProperties = type.GetProperties(BindingFlags.SetProperty | BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance)

.Where(propInfo => propInfo.GetCustomAttributes(typeof(ColumnAttribute), true).Length > 0)

.ToArray();

return matchingProperties.Length == 0 ? null : matchingProperties;



}

}

Sql Server PIVOT Example

very good example from http://www.insidesql.org/blogs/cmu/sql_server/pivot-mit-2-wertefeldern

Set Nocount on
go
CREATE TABLE #p
( Name varchar(10),
ErfüllungsgradIst INTEGER,
ErfüllungsgradSoll INTEGER,
Maschine varchar(20) )
GO
INSERT INTO #p VALUES ('Maier', 3, 4, 'Drehbank')
INSERT INTO #p VALUES ('Maier', 2, 3, 'Fräsmaschine')
INSERT INTO #p VALUES ('Maier', 4, 4, 'Bohrmaschine')
INSERT INTO #p VALUES ('Huber', 1, 2, 'Drehbank')
INSERT INTO #p VALUES ('Huber', 2, 3, 'Fräsmaschine')
    
select *
from #p
      
Select Name, 'ErfüllungsgradSoll' as Erfüllungsgrad, coalesce(Drehbank, 0) as Drehbank,
coalesce(Fräsmaschine, 0) as Fräsmaschine, coalesce(Bohrmaschine, 0) as Bohrmaschine
from
(Select Name, ErfüllungsgradSoll , Maschine from #p) as SourceTable
PIVOT
(
min(ErfüllungsgradSoll)
FOR Maschine IN ( [Drehbank],[Fräsmaschine], [Bohrmaschine])
) as PivotTable
Union ALL
Select Name, 'ErfüllungsgradIst', coalesce(Drehbank,0), coalesce(Fräsmaschine,0), coalesce(Bohrmaschine, 0)
from
(Select Name, ErfüllungsgradIst, Maschine from #p) as SourceTable
PIVOT
(
min(ErfüllungsgradIst)
FOR Maschine IN ( [Drehbank],[Fräsmaschine], [Bohrmaschine])
) as PivotTable
Order By Name, Erfüllungsgrad
     

GO
drop Table #p

LINQ Query / Method Syntax Examples

Entity Framework

for Entity Framework: Load referenced Object first, then query Attribute of Object:
parent.Childs.Select(o=>o.ChildAttribute).FirstOrDefault(x => x.Name == name);

LINQ Query / Method Syntax Examples

Customer[] customers = Service.GetCustomers();
var query = from customer in customers
where customer.Name == "Hans"
from order in customer.Orders
where order.Quantity > 6
select new {order.OrderID, order.ProductID};

Customer[] customers = Service.GetCustomers();
var query = customers
.Where(c => c.Name == "Hans")
.SelectMany(c => c.Orders)
.Where(order => order.Quantity > 6)
.Select(order => new {order.OrderID, order.ProductID});

Entity Framework (EF) Code First Migrations: Seeding

you can use SQL(...) in Up/Down Methods, or Configuration.Seed or check if already exists

Entity Framework (EF) Code First Migration Update

If you want to update an existing database to current Version you have to set the EF Database Initializer:
System.Data.Entity.Database.SetInitializer(new MigrateDatabaseToLatestVersionContext, Migrations.Configuration>());

when you first Access the db, EF checks the current Version and updates it if necesarry. You Need a parameterless contructor in your Context Class. Ist called several times when updating the DB. And you have to call the base constructor (of DBContext) and pass the correct connectionstringname if you want the correct database beiing updated, else the Default database is being updated (db Name=Namespace.classname of ypur EF Context)

Visual Studio: customize debugger display of classes

Shows Name, type if not null, if type null then Shows "Null"
e.g.:
Name = "Stückzahl", Type="Int"
Name = "Int", Type="Null"

class head:

[DebuggerDisplay("Name = {Name}, Type={null==Type?\"Null\":Type.Name}")]

public class DbAttribute



{




public Guid DbAttributeId { get; set; }

public string Name { get; set; }

public DbAttribute Type { get; set; }

sql server display all user columns with nvarchar(Max) or normal DateTime

Entity framework (EF) Code First default uses nvarchar(max) for string - thats a performance overhead.


check all columns with nvarchar(max):

select t.name as tablename,c.name as columnname,c.max_length from sys.columns c join sys.tables t on c.object_id=t.object_id Where c.max_length=-1


check all columns with normal datetime (EF better use datetime2):

select t.name as tablename,c.name as columnname,c.max_length from sys.columns c join sys.tables t on c.object_id=t.object_id where system_type_id=61

Visual Studio Einstellungen / Preferences

Tools – Options – Projects and Solutions – Track Active Item in Solution Explorer

Entity Framework Code First Relations one Foreign Key to manyTables

the only possibility to use one foreign key column in Table N for more then one Oneside Table in Code first  is to make a base class (OneSidebase) for the 1:n relation and derive from this class OneDiseBase for all tables (OneSide1 ...m) that Need a 1:n relation to Table N

SqlSkills

source of good tipps:
https://www.sqlskills.com/

powershell files and directory

#is there any *.Bak file ?

if (Get-ChildItem C:\sqlBackup\Stp1\*.Bak) {"exists"} else {"no"}

#create Dir if not exists and don't Display error if exists:
md mydir -Force

sql server: reorg / rebuild fragmented indizies (blows up transaction log)

--0) tables with no primary key - create key first to reorg / rebuild indezis

select s.name, o.name, I.Name

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where I.name is null order by s.name,o.name







--1) reogranize indexes

select s.name, o.name, I.Name,st.avg_fragmentation_in_percent,

'alter index '+i.name +' on ' + o.name + ' Reorganize;'

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where st.avg_fragmentation_in_percent>5 and I.name is not null

order by st.avg_fragmentation_in_percent desc







--2) rebuild indezies where reorg hasn't succeeded

select s.name, o.name, I.Name,st.avg_fragmentation_in_percent,

'alter index '+i.name +' on ' + o.name + ' Rebuild;'

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where st.avg_fragmentation_in_percent>5 and I.name is not null

order by st.avg_fragmentation_in_percent desc

power shell: youngest entry in dir

Get-ChildItem | Sort-Object -Property @{Expression={$_.LastWriteTime};Ascending=$false} | Select-Object -First 1

sql server and power shell

#execute tsql script using actual Windows user:


invoke-sqlcmd  -serverinstance "localhost\SQLEXPRESS" -Query "select * from sys.database_files"


invoke-sqlcmd -inputfile "Backup.sql" -serverinstance "localhost\SQLEXPRESS" # -database "mydatabase"




#backup database - since sqlserver 2012 (2008 not working!)
Backup-SqlDatabase -ServerInstance "localhost\SQLEXPRESS" -Database "mydb" -BackupFile "c:\sqlBackup\mydb.Bak"


sometimes ist necesarry to Import sqlSnapIns:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

normale Shell: sqlcmd -S localhost\SQLEXPRESS

EF Code First Migrations Basics

EF (Entity Framework) Migrations are a way to generate versioned Database Scripts from c# Classes. Developpers can specify which Changes are sumerized to a new Version. EF Migrations are enabled by Package Manager Console Command Enable-Migrations. It creates a Migration Folder and a Configuration Class in your Visual Studio Project.
When all changes for a new Version are made to the Entity c# Classes, the Package Manager Console Command "Add Migration (Migrationname)" creates a c# Class deriving from DbMigrations with a Up and a Down Method, which executes the necesarry SQL for Up/Downgrading the DB. The DB itself is upgraded either by the Package Manager Console Command Update-Database (which also lets you create script by -script Parameter) or by using Database Initializer, which are executed when the DBContext first Needs the Database:
//upgrades Production Datebase, maybe first make a backup
Database.SetInitializer(new MigrateDatabaseToLatestVersion());
//creates new Db
Database.SetInitializer(new CreateDatabaseIfNotExists());

So the Software itself can automatically create or upgrade the needed Database.
Summary of Commands and Options: https://coding.abel.nu/2012/03/ef-migrations-command-reference/ or in the Package Manager Console: get-help Add-Migration

most important Package Manager Console Commands:

1. Enable-Migrations
2. Add Migration (Migrationname)
3. Update-Database

Examples:

getting all migrations which have been deployed to a database so far:
get-migrations

adding a migration after changing EF-Relevant Code:
add-migration Mig0025

update Database to a specific Migration
Update-Database -TargetMigration Mig0005 -Verbose -connectionStringName Db1

Add Custom SQL to Migration

use the SQL Methode in the Up / Down Methode:

EF Code First Migrations Basics

EF (Entity Framework) Migrations are a way to generate versioned Database Scripts from c# Classes. Developpers can specify which Changes are sumerized to a new Version. EF Migrations are enabled by Package Manager Console Command Enable-Migrations. It creates a Migration Folder and a Configuration Class in your Visual Studio Project.
When all changes for a new Version are made to the Entity c# Classes, the Package Manager Console Command "Add Migration (Migrationname)" creates a c# Class deriving from DbMigrations with a Up and a Down Method, which executes the necesarry SQL for Up/Downgrading the DB. The DB itself is upgraded either by the Package Manager Console Command Update-Database (which also lets you create script by -script Parameter) or by using Database Initializer, which are executed when the DBContext first Needs the Database:
//upgrades Production Datebase, maybe first make a backup
Database.SetInitializer(new MigrateDatabaseToLatestVersion());
//creates new Db
Database.SetInitializer(new CreateDatabaseIfNotExists());

So the Software itself can automatically create or upgrade the needed Database.
Summary of Commands and Options: https://coding.abel.nu/2012/03/ef-migrations-command-reference/ or in the Package Manager Console: get-help Add-Migration

most important Package Manager Console Commands:

1. Enable-Migrations
2. Add Migration (Migrationname)
3. Update-Database

Examples:

getting all migrations which have been deployed to a database so far:
get-migrations

adding a migration after changing EF-Relevant Code:
add-migration Mig0025

update Database to a specific Migration
Update-Database -TargetMigration Mig0005 -Verbose -connectionStringName Db1

Add Custom SQL to Migration

use the SQL Methode in the Up / Down Methode:

Entity Framework Basics

Entity Framework is a open source OR Mapper from Microsoft. It is installed via NuGet Package Manager to a Visual Studio Project.
There are 3 main ways to use it:
1.Database first: By Adding a new Entity Framework Model you can choose "from existing Database". A Wizard let you select the objects you want to generate Entity Model and Entity Classes for.
2.Model first: By Adding a new Entity Framework Model you can choose "Empty Model". then you can add Entities in the Model Designer - Entity c# Classes are generated when you build the Project, you can deploy it to a database by choosing "Update Database" from the Context Menu in the Model Designer.
3.Code first: You write c# classes and add them as DbSet to a Entity Context Class (derives from System.Data.Entity.DbContext). Then the DBContext creates a Database with Tables for every Class in a DbSet Property and for all related classes. It also creates Relations.

simple wpf entity framework databinding to ms DataGridView and Telerik RadGridView

main Xaml Window: Codebehind: namespace WpfApplication1 { ///

/// Interaction logic for MainWindow.xaml ///

public partial class MainWindow : Window { StpDbEntities1 _stpDbEnt = new StpDbEntities1(); public MainWindow() { InitializeComponent(); } private void Window_Loaded(object sender, RoutedEventArgs e) { MyDataGrid.ItemsSource = _stpDbEnt.tMachine.Local; MyRadGridView.ItemsSource = _stpDbEnt.tMachine.Local; _stpDbEnt.tMachine.Load(); //Window1 w1=new Window1(); //w1.Show(); //Show first Name - not necesarry var machinename = _stpDbEnt.tMachine.First().Name; if (machinename == null) throw new ArgumentNullException("machinename"); MessageBox.Show(machinename); } private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e) { _stpDbEnt.SaveChanges(); } } }

power shell for loop

for ($i=1; $i -le 10; $i++) { Write-Host "Loop:" $i }

Creating some sql server Databases per Tsql script

declare @counter int = 1 declare @sql nvarchar(50) while @counter < 100 begin set @counter = @counter + 1 set @sql = 'Create Database T'+ convert(nvarchar(3),@counter) print ' sql=' + @sql exec (@sql) end

oracle kill sessions

select inst_id,sid,serial# from gv$session where schemaname like 'PSA%'; alter system kill session '14,24893,@1';

oracle constraints

select * from user_Constraints where constraint_name='myConstraint';

news visual studio VS 2013 neu

Save Load Color SChema Blue Alt F12 CRT, Scrollbar Options Alt+Cursor Down / Up verschiebt Codeblöcke Search in Options, Fenstergröße änderbar XAML F12, INtllisense für Resourcen Code Lenses Visual Studio Blog

oracle datetime examples

to_date('22.6.2015 12:03','DD.MM.YYYY HH24:MI') alter table myTable add PERFORMED_AT DATE; --TIMESTAMP stores date and time alter table myTable modify PERFORMED_AT TIMESTAMP;

last / letzte oracle sql statements

select vsession.machine,service_name,vsession.username,vcursor.sql_id,vsql.last_load_time,vsql.executions,vcursor.sql_text as short_sql,vcursor.sid,vsession.program,vsession.osuser,vsql.sql_fulltext as long_sql from v$open_CURSOR vcursor LEFT OUTER JOIN v$sql vsql on (vcursor.sql_id = vsql.sql_id) INNER JOIN v$session vsession on (vcursor.sid = vsession.sid) WHERE vsql.sql_fulltext like '%ETL%' order by vsql.last_load_time;

oracle Invalide Packete / Packets

SELECT owner,object_name,subobject_name,object_type, status FROM all_objects WHERE status='INVALID';

einfaches / simple power shell script

[CmdletBinding()] param( [Parameter(Mandatory=$True)] [string[]] $Computername='localhost' ) Get-VM -ComputerName $Computername with help: <# .Synopsis short Explanation .Description long Explanation .Parameter Computername paramdesc .Example getVm localhost #> [CmdletBinding()] param( [Parameter(Mandatory=$True)] [string[]] $Computername='localhost' ) Get-VM -ComputerName $Computername

nützliche power shell cmdlets

update-help help alias Import-PSSession New-PSSession Get-PSSession help exit-PSSession -ShowWindow icm $env:PSModulePath -split ";" 's1','s2' | foreach {write-output $_}

vs (visual studio) shotcuts

shift Alt Enter ... Fenster groß / klein
 Vs2013: Alt +Pfeiltaste: Zeile verschieben
 Strg Alt Space ... toggle INtellisense Mode
 Strg-Shift-V Rotate Zwischenablage
 crt-shift Mausrad Zoom
 crt k,s : surround with
 crt F10 debug till curser

wichtige EInstellungen:
Optionen / Projekte und Solutions / Aktives Element im Projektmappen Explorer überwachen

powershell drivesize remaining

PS C:\Windows\system32> icm hv01,hv02,hv04 {Get-Volume} | select pscomputername, driveletter,sizeremaining | Format-Table -AutoSize

sql disk io

short reblogged from http://www.mssqltips.com/sqlservertip/2127/benchmarking-sql-server-io-with-sqlio/ 1) change param.txt to bigger testfile (10Gb) and correct drive: param.txt c:\testfile.dat 8 0x0 10240 #name threads cpuaffinity sizeInMB 2) create testfile: sqlio -kW -s5 -fsequential -o4 -b64 -Fparam.txt C:\Program Files (x86)\SQLIO>sqlio -kW -s5 -fsequential -o4 -b64 -Fparam.txt sqlio v1.5.SG parameter file used: param.txt file c:\testfile.dat with 8 threads (0-7) using mask 0x0 (0) 8 threads writing for 5 secs to file c:\testfile.dat using 64KB sequential IOs enabling multiple I/Os per thread with 4 outstanding size of file c:\testfile.dat needs to be: 10737418240 bytes current file size: 0 bytes need to expand by: 10737418240 bytes expanding c:\testfile.dat ... done. using specified size: 10240 MB for file: c:\testfile.dat initialization done CUMULATIVE DATA: throughput metrics: IOs/sec: 1327.03 MBs/sec: 82.93 VM on HyperV on SSD Raid,other examples: Al: IOs/sec: 3862.24 MBs/sec: 241.39 3) RANDOM WRITE TEST: (-d ... Drive -s seconds -t threads simultan) sqlio -dC -BH -kW -frandom -t1 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kW -frandom -t2 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kW -frandom -t4 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kW -frandom -t8 -o1 -s60 -b64 \testfile.dat C:\Program Files (x86)\SQLIO>sqlio -dC -BH -kW -frandom -t1 -o1 -s90 -b64 \testfile.dat sqlio v1.5.SG 1 thread writing for 90 secs to file C:\testfile.dat using 64KB random IOs enabling multiple I/Os per thread with 1 outstanding buffering set to use hardware disk cache (but not file cache) using current size: 10240 MB for file: C:\testfile.dat initialization done CUMULATIVE DATA: throughput metrics: IOs/sec: 1082.02 MBs/sec: 67.62 Al -t1: IOs/sec: 915.90 MBs/sec: 57.24 4)RANDOM READ TEST: sqlio -dC -BH -kR -frandom -t1 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kR -frandom -t2 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kR -frandom -t4 -o1 -s60 -b64 \testfile.dat sqlio -dC -BH -kR -frandom -t8 -o1 -s60 -b64 \testfile.dat C:\Program Files (x86)\SQLIO>sqlio -dC -BH -kR -frandom -t1 -o1 -s30 -b64 \testfile.dat sqlio v1.5.SG 1 thread reading for 30 secs from file C:\testfile.dat using 64KB random IOs enabling multiple I/Os per thread with 1 outstanding buffering set to use hardware disk cache (but not file cache) using current size: 10240 MB for file: C:\testfile.dat initialization done CUMULATIVE DATA: throughput metrics: IOs/sec: 2409.36 MBs/sec: 150.58 Al: IOs/sec: 278.00 MBs/sec: 17.37

powershell examples

get-host update-help Get-ExecutionPolicy Get-PSDrive get-vm -ComputerName hv01,hv02,hv04 | where State -eq Off | sort name help New-SelfSignedCertificate -Examples New-SelfSignedCertificate -DnsName www.test.net -CertStoreLocation Cert:\LocalMachine\My $var=read-host "Enter a Computername" Write-Output "Test" Write-Host "Test" Write-Warning "Test" 's1','s2' | foreach {write-output $_}

values constructor sql server

select * from (values (10),(20)) as TabName(colName)

kiwi syslog daily logfile

Default/Actions/Log to file C:\Programme\Syslogd\Logs\Interfaceueberwachung\log%DateISO.txt

Sql Server Performance optimierung

set statistics io, time on;