forked from dotnet/efcore
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathProgram.cs
More file actions
145 lines (127 loc) · 4.76 KB
/
Copy pathProgram.cs
File metadata and controls
145 lines (127 loc) · 4.76 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
// Copyright (c) .NET Foundation. All rights reserved.
// Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information.
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
#pragma warning disable 169
namespace Samples
{
public class Program
{
private static void Main()
{
SetupDatabase();
using (var db = new BloggingContext())
{
#region Query
var postCounts = db.BlogPostCounts.ToList();
foreach (var postCount in postCounts)
{
Console.WriteLine($"{postCount.BlogName} has {postCount.PostCount} posts.");
Console.WriteLine();
}
#endregion
}
}
private static void SetupDatabase()
{
using (var db = new BloggingContext())
{
if (db.Database.EnsureCreated())
{
db.Blogs.Add(
new Blog
{
Name = "Fish Blog",
Url = "http://sample.com/blogs/fish",
Posts = new List<Post>
{
new Post { Title = "Fish care 101" },
new Post { Title = "Caring for tropical fish" },
new Post { Title = "Types of ornamental fish" }
}
});
db.Blogs.Add(
new Blog
{
Name = "Cats Blog",
Url = "http://sample.com/blogs/cats",
Posts = new List<Post>
{
new Post { Title = "Cat care 101" },
new Post { Title = "Caring for tropical cats" },
new Post { Title = "Types of ornamental cats" }
}
});
db.Blogs.Add(
new Blog
{
Name = "Catfish Blog",
Url = "http://sample.com/blogs/catfish",
Posts = new List<Post>
{
new Post { Title = "Catfish care 101" },
new Post { Title = "History of the catfish name" }
}
});
db.SaveChanges();
#region View
db.Database.ExecuteSqlCommand(
@"CREATE VIEW View_BlogPostCounts AS
SELECT Name, Count(p.PostId) as PostCount from Blogs b
JOIN Posts p on p.BlogId = b.BlogId
GROUP BY b.Name");
#endregion
}
}
}
}
public class BloggingContext : DbContext
{
private static readonly ILoggerFactory _loggerFactory
= new LoggerFactory().AddConsole((s, l) => l == LogLevel.Information && !s.EndsWith("Connection"));
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
public DbQuery<BlogPostsCount> BlogPostCounts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(
@"Server=(localdb)\mssqllocaldb;Database=Sample.QueryTypes;Trusted_Connection=True;ConnectRetryCount=0;")
.UseLoggerFactory(_loggerFactory);
}
#region Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Query<BlogPostsCount>().ToTable("View_BlogPostCounts")
.Property(v => v.BlogName).HasColumnName("Name");
}
#endregion
}
#region Entities
public class Blog
{
public int BlogId { get; set; }
public string Name { get; set; }
public string Url { get; set; }
public ICollection<Post> Posts { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
}
#endregion
#region QueryType
public class BlogPostsCount
{
public string BlogName { get; set; }
public int PostCount { get; set; }
}
#endregion
}