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 $_}