0:00

Psst, how'd you like to listen to

0:02

DotNet Rocks with no ads? Easy,

0:05

become a patron. For

0:07

just $5 a month, you get access to

0:09

a private RSS feed where all the shows

0:11

have no ads. $20

0:14

a month will get you that and a

0:16

special DotNet Rocks patron mug. Sign

0:19

up now at patreon.dotnetrocks.com.

0:22

Hey, Carl and Richard here with your 2024 NDC schedule.

0:26

You'll be at as many NDC conferences as

0:28

possible this year and you should consider attending

0:31

no matter what. NDC Oslo is happening June

0:33

10th through the 14th. Get

0:36

your tickets at ndcoslo.com. The Copenhagen

0:38

Developers Festival happens August 26th through

0:40

the 30th. Early

0:42

Bird Discount ends April 26th. Tickets

0:46

at cphdevfest.com. NDC

0:49

Porto is happening October 14th through the

0:51

18th. The Early Bird

0:53

Discount ends June 14th. Tickets

0:56

at ndcporto.com. And

0:59

we'll see you there. We hope. Hey,

1:07

welcome back to DotNet

1:09

Rocks. I'm

1:16

Carl Franklin. And I'm Richard Campbell. I'm

1:18

in my studio and Richard

1:20

is in Sweden. I am

1:22

Sweden. I'm

1:25

in the castle comms in

1:27

Oerbro. Nice. Just outside of

1:29

Stockholm. Very nice. Yeah. And

1:31

we're going to have a really good talk today with

1:34

Georgi. But first, let's

1:36

do Better Know Framework. Awesome.

1:39

What do you got? Richard,

1:47

you probably saw this already. DARPA

1:50

revealed that an experimental X-62A

1:55

AI-controlled jet successfully

1:57

faced the human

1:59

pilot During an in-air

2:01

dogfight test carried out last

2:03

year. Yeah, you probably

2:05

read about it when it happened But yeah,

2:07

well and to be clear an x62 is

2:10

an f60. Yeah, it's just customized Yeah, they

2:12

were already fly by wire So they just

2:14

added in this new set

2:16

of modules to fly itself and apparently

2:19

human pilots were on board With controls

2:21

to disable the AI system, but DARPA

2:23

says the pilots didn't need to use

2:25

the safety switch at any point So

2:28

isn't that interesting? And a

2:30

little scary, but you know Just

2:33

warfare. I guess I

2:35

would rather have something being flown by wire

2:37

than flown by a pilot

2:39

But you know for the

2:42

pilot's sake anyway, yeah, apparently recently they

2:44

put the Head of the

2:46

Air Force in the backseat of the f-16. They did the

2:48

same thing So oh wow Bringing

2:52

to light that this is how good

2:54

the software is now boys, right? Yeah,

2:56

it does bring up a little Star

2:58

Wars heat Unced you know, yeah, although,

3:00

you know curiously they modeled all the

3:02

Star Wars battles after World War one

3:04

air dog air dogfight So yeah,

3:07

I was I was thinking of episode

3:10

one You

3:12

know where all the all the little

3:14

drones were flying around Yeah So

3:16

like little like little gnats like

3:18

little mosquitoes just shooting darts at people

3:21

and I thought oh my god Is that what we have to look forward

3:24

to? Well, anyway, yeah,

3:26

the Air Force has this project called

3:28

loyal wingman Which

3:30

is unmanned vehicles that they that

3:32

the pilots are flying with So

3:36

things are getting interesting. Yep. They certainly are

3:40

So anyway, that's what I got who's talking to us today,

3:42

Richard grab your comment of a show 1855

3:45

the one we did with Ted Neward back in July

3:47

last year We were talking about

3:49

multiple types of data stores and called it

3:52

multimodal. Mm-hmm fun And

3:54

Andy Perth had this somewhat long comment, but I

3:56

thought it was relevant to what we were talking

3:58

about today He says if ORM is the Vietnam

4:00

of computer science. And of course, we

4:02

were talking to Ted Neward. That's a

4:04

reference to an old show. Old set of

4:06

blog posts too. And my repeated trips back

4:09

to some kind of ORM must make me

4:11

the US State Department. Ted would appreciate my

4:13

citing that paper when we were discussing how

4:16

to layer an API on top of

4:18

an insanely complex XML model back around

4:20

2007. You know, that's a

4:23

whole complaint about N-hybridate was that the

4:25

XML was out of control, right? So

4:28

I have to snark at your comments on the

4:30

musing of using XML to describe XML schema on

4:32

two grounds. The first is XML

4:35

bodies. First tried

4:37

non-XML formats with DTD. Okay, that did

4:39

suck more. That's true. Yeah, it did.

4:41

And two, slipping even further down the

4:43

rabbit hole of stuff for XML should

4:45

be XML. They went even further to

4:47

add a programming language called XSLT, which

4:49

I did some work in, which

4:52

is written in XML and should

4:54

cure anyone if ever complaining about

4:56

mere semicolons as too much punctuation.

4:58

Yeah. You never used brackets in

5:01

your life until you wrote XSLT

5:03

like God. You

5:05

know, that's why we had tools to write

5:08

that stuff. Yeah, it wasn't very friendly. It

5:11

wasn't a tool. For

5:14

my sins, I used to teach a multi-day

5:16

course on the W3XML schema

5:19

to geologists and mining engineers.

5:21

That insanely complex model encompassed

5:23

geospatially located ore bodies described

5:25

as full 3D structures somewhere

5:28

in Australia. It also

5:30

included laboratory assay results for

5:32

sampling cores to find such

5:34

bodies. All that salted with

5:36

a dash of Sarbanes Oxley traceability on

5:39

top. And that experience

5:41

taught me two things. One, never try to

5:43

get scientists from different disciplines to agree on

5:45

a definition. And to XML

5:47

can respect complex object graphs, liberal

5:49

use of the ID attribute is

5:51

the key. Amazing, extensible results

5:53

are possible. So keep the government theme

5:56

and borrow a sentiment from Winston Churchill.

5:58

I'd like to remoze that. that XML

6:00

is the democracy of data structures.

6:03

Well, TBL liked it, but

6:05

even he backed off eventually. Oh,

6:07

man. Yeah,

6:11

I love it, but yeah, Andy, you're a

6:13

true believer. I like it. And

6:15

so a copy of Musicobuy is on its way to

6:17

you, and if you'd like a copy of Musicobuy, write

6:19

a comment on the website at dotnetrocks.com or

6:21

on the Facebooks, we publish every show there. And if you

6:24

comment there and I read it on the show, we'll send

6:26

you a copy of Musicobuy. Hey, did I tell you that

6:28

Code in a Castle is back on? I

6:30

heard rumor. Yeah, so it's gonna be the

6:32

last week of September, and

6:35

it's in the same castle in Tuscany. So

6:38

if you missed out and

6:40

you regretted it, go

6:42

check it out, Code in a Castle. And also

6:44

the video from last year is up there. So

6:47

anyway, that's all I wanna say about that. All

6:49

right, let's bring Georgi An, Georgi

6:51

Dalakishvili is a software developer

6:54

with more than a decade

6:56

of experience. He works

6:58

mainly with C-sharp, asp.net Core, REST, Maui,

7:01

Xamarin, Entity Framework Core, AWS,

7:03

and SQL Server. So,

7:05

you know, just a couple of things. Couple of things. Interests here

7:08

and there, just a couple of things. He

7:10

has also worked with many other frameworks

7:12

and systems such as GraphQL, asp.net MVC,

7:15

asp.net Web API, WCF,

7:17

Avalonia, WinForms, Oracle, and

7:20

others. In short, he doesn't know what

7:22

he wants to be when he grows up. Georgi

7:25

is an open source author and

7:27

contributor and a member

7:29

of the .NET Foundation. Welcome, Georgi. Thanks

7:32

for inviting me to the .NET

7:34

Rocks. I have been listening to it

7:37

since 10 years already, I think.

7:40

So it's a pleasure to be on the

7:42

show. Awesome, yeah, well, you're welcome. It's great

7:44

to have you here. We

7:47

are really getting used to a lot

7:50

of the people that spent their

7:52

career listening to .NET Rocks now being

7:54

guests. So that's

7:56

very cool. It's very cool to have you. So you're here. to

8:00

talk about EF queries and

8:02

debugging them visually. So

8:05

that caught my interest. What do you

8:07

mean by that? Yes.

8:10

So one of the

8:12

biggest – well,

8:16

not disadvantages, but one of

8:18

the biggest downsides of using an

8:22

ORM and any kind

8:24

of ORM, probably not just an NIT framework core,

8:26

is that it hides

8:29

the SQL behind

8:31

the linked queries

8:33

and it

8:36

tries to abstract all

8:38

the database-related stuff behind

8:40

the strongly-typed queries and

8:43

C-sharp, which is

8:45

very comfortable, definitely. But

8:47

also can cause some performance

8:50

issues if you are not careful

8:52

writing your queries. Some

8:55

people think that you can

8:57

just learn EF

8:59

Core and you don't need to look

9:01

at the analogy to

9:04

SQL or SQL is an

9:06

old language and we don't need it anymore.

9:09

Yeah. It's kind of like if your car

9:11

is going along fine and then it just

9:14

starts going very slow and you think, oh,

9:16

this car stinks. I'm going to bring it

9:18

into the shop rather than having

9:20

a look under the hood and actually trying

9:22

to see what's going on. You might discover

9:25

the problem. Yeah. Actually,

9:27

it's not as frightening as

9:30

some people probably think.

9:34

For me personally, when

9:39

I started programming about 15 or

9:41

17 years ago, there was no ORM. If

9:43

you needed to

9:48

interrupt this database, you

9:51

had to write your ad hoc

9:53

SQL in C-sharp code

9:55

or storage procedures, which

9:57

was even more painful.

10:00

meaningful and so

10:02

yeah, the entity

10:04

framework is definitely nice but

10:07

you should also definitely understand

10:09

the SQL behind it

10:11

and it will change

10:15

the way you write your entity

10:17

framework queries and it will also

10:19

make your code and

10:22

your application more performance

10:24

definitely. So is it easy to

10:26

discover what SQL

10:28

or SQL is being

10:31

created under the hood and sent to the SQL Server

10:34

Engine with the entity framework? Yes,

10:36

so even without the extension set I

10:38

wrote that we will be talking about

10:41

in a couple

10:43

of minutes, it's still very

10:45

easy. With entity framework

10:47

you can either, I think it's tool

10:50

SQL strength within method

10:52

in entity framework that's

10:55

to get the SQL and use

10:57

the SQL or you

10:59

can just configure login in

11:01

your application and when you develop

11:04

your application you will find use

11:06

the generated SQL in the output

11:08

window of Visual

11:10

Studio. Let's go back

11:12

to SQL string, is that extension available

11:14

on the connection, on the DV set,

11:17

on the... It's

11:20

available on

11:22

iQueryable as far as I

11:25

remember so you just take

11:27

your query and call this,

11:30

it's called tool query string if I

11:32

remember it correctly and

11:34

you will get the query

11:36

string programmatically or as I said

11:38

you can configure login and you

11:41

can use the SQL statements

11:43

in the output window of Visual

11:46

Studio. You also hinted at

11:48

a tool that you wrote that

11:50

makes it much easier so let's

11:52

talk about that, that sounds fun. Yes,

11:55

so my tool goes a little

11:57

bit beyond just getting the...

12:00

SQL query. So this

12:02

is Visual Studio extension,

12:04

you download it and

12:06

install directly from the marketplace.

12:09

You can do it directly inside

12:11

Visual Studio without relink it and

12:15

just searching for it in the

12:17

extensions. And

12:19

essentially, it's a debugger visualizer.

12:22

So a debugger visualizer is

12:24

a kind of extension that

12:27

lets you visualize the

12:31

objects when you hover your mouse

12:33

pointer as a

12:35

variable. Nice. So just like

12:38

you can view the string

12:41

or XML or JSON in

12:44

a nicely formatted way in Visual Studio.

12:47

When you hover your pointer on

12:49

the I query variable inside

12:51

Visual Studio, a new item

12:53

will appear in the I

12:55

query variable, whatever it is

12:57

called in Visual Studio and

13:00

it will say query plan

13:02

visualizer. So when you click

13:04

on it, the

13:06

extension will actually fetch the

13:08

query application plan from the

13:11

database and display

13:13

it in a nice graphical

13:15

HTML formatted inside the web

13:17

view. So you can use

13:19

the query itself. And also

13:22

you can use the plan that

13:24

the database executed.

13:26

Well, you can

13:29

use the plans that the database

13:31

generated to execute your query

13:34

under the hood. Can you tell us one more

13:36

time what that Visual Studio

13:38

extension is called? Yeah, it's

13:40

called the F core dots

13:42

visualizer. Okay. And it says

13:44

using your entity framework dot exceptions library

13:46

under the hood, which is the next

13:48

thing I'd like to talk about? No,

13:51

no, it it's done.

13:54

It's separate. Completely

13:56

separate. Yeah, that's a separate library.

13:58

Okay. So Mike's experience has

14:00

been if you've got a problem with

14:02

an NDE framework call that

14:05

you should just rewrite it to call

14:07

a stored procedure. That's

14:11

the workaround. I really appreciate

14:13

what you're doing here that, yeah,

14:15

I've written an expression in link and

14:17

that translates into this query in SQL

14:19

Server and it's a table scan so

14:21

it's sucking. But

14:26

I mean, I guess the question is, is someone actually

14:28

going to modify the database to make this perform better

14:30

or do we just switch to

14:32

a stored procedure and then the DBA

14:34

can optimize the stored procedure? Well,

14:37

Richard, you're opening a can of worms

14:39

here, man. Wouldn't you first

14:41

time? We're going back to the Oranini

14:43

versus Ted Neward smack down here. Yeah.

14:48

What do you think? Anyway, G.R.G., what do you

14:50

think? Yeah. Well,

14:53

no, you don't necessarily have to turn

14:55

it into a stored procedure. Thanks, Scott.

14:58

I think you can. Well, in most cases, it's

15:02

either a missing index from your

15:05

table or your

15:08

query is just written in a

15:10

way that can't utilize the existing

15:12

indexes on the table or

15:14

maybe in some cases, you

15:17

just have to redesign your

15:19

table because it's not

15:22

designed the most of the way. So

15:25

your queries are going to be

15:27

slow nowhere, no matter how hard and

15:29

no matter what you try.

15:32

Yeah. I just question is, I

15:34

didn't write that query and the framework wrote it. If

15:37

it's not utilizing my index as well, what

15:39

are my choices here? Like what do you,

15:41

what are you actually able to do? So

15:44

the most common reasons that

15:47

I have seen that

15:49

your query does not use indexes is,

15:51

for example, so this

15:53

is, there is a term searchable or

15:55

searchable queries, I think. So

15:58

if you have a condition like. there

16:00

is a date of birth, for example

16:02

in the database table and you want

16:04

to find all the

16:07

records where the year is for

16:09

example 2000. And

16:12

if you just call

16:15

the date part function

16:18

in SQL Server or dates.year

16:21

in C-sharp and compare it

16:23

to 2000, then

16:27

it's impossible for the database

16:29

to use an interesting index

16:31

even if it's there because

16:34

for every record it has to calculate the

16:36

year from the dates on the fly and

16:40

it can't use the index. Yeah. So it's now

16:42

going to process every row to do that for

16:44

you. Yeah. Whereas if you gave it a range,

16:48

then it could actually write a better query that it

16:51

could be in there. So yeah, so

16:53

in this case, for example, the

16:55

solution will be to find all

16:57

records where date of birth is

16:59

more than 2001 January and

17:02

less or equal than 2000 December 31.

17:06

Right. And in this case, you don't have any

17:09

more calculations on the fly. You

17:12

just give the range as you said

17:14

that you want all the records where

17:16

the date of birth value is from

17:18

this value until this value. And as

17:21

long as the index is

17:24

on the table, it will use

17:26

the index and it will be a lot

17:29

faster because it will just scan the subset

17:32

of the rows that it needs to return

17:35

your results. Right. So that's the

17:37

most common mistake I think I

17:39

have seen. And it's also easy

17:41

to make that mistake because when

17:43

you start writing your query, it's

17:46

natural to write it like that if

17:48

you don't think what's going on under

17:50

the hood. Yeah. So that's one of

17:52

the reasons why it's important to understand

17:55

what's going when you

17:57

write a query in C-sharp, which... And

18:00

you can't also blame the

18:02

developers all the way because if

18:04

you have just 100 or 1000 rows in

18:07

your test database, it is going to be

18:09

fast. So they can't even notice that there

18:11

is a problem under the hood. No, and

18:13

then you have a million rows and you

18:16

notice right away. And you could still

18:18

blame the developers. Let's face it. But

18:20

I could see, you know, setting a date range

18:22

is kind of a nuisance because

18:25

like, do you really go, you know,

18:27

greater than equal to 2001, 01, 01, and you need time to

18:29

0, 0, 0, 0, 0, and less than, you know, 1231 and

18:31

2359, 59.999 or, you know, or that'd be less than equal to

18:33

like. Richard,

18:46

we said we weren't going to quote code on this

18:48

show. We weren't going to read

18:50

code. But it's such a dev

18:52

thing to say, hey, if the year in this

18:54

date is 2000, get it. It's

18:58

not understanding that you just defeated all indexing,

19:00

like the index doesn't have a chance because

19:03

it's not sargeable. Because

19:06

Georgie pulled that one out today. Correct

19:11

term, but not everybody knows it. But yeah,

19:13

okay. But I mean, they're nice. The

19:15

point here is you're presumably as a developer, you're

19:18

also responsible to database and you're just trying to

19:20

figure out why. And so

19:22

you've assembled all the bits together to say, this

19:24

is the query that's generated. Here's the query

19:26

plan that it did. Yeah.

19:28

Now go figure it out. Like you

19:31

now got the ingredients to say, can

19:33

I write this as an expression that will query more effectively? Or

19:36

do I have to go to all the other approaches? Yeah.

19:39

Another thing to consider is if we

19:41

use the same example, like

19:44

if you are searching for records where

19:46

the date of birth is larger

19:48

than the year 2000, then it may

19:50

use index even if you write it

19:53

correctly. But if

19:55

the condition changes and you want records,

19:58

for example, from nine years, 1950 and It

20:02

turns out this like 95% of your

20:04

records in your database have data pairs

20:07

which is higher than 1950

20:09

or 1920 or whatever send

20:12

a database may decide that it doesn't make

20:14

sense to use index because I Have

20:17

to return all the almost all the

20:19

records that are easy table. So why

20:21

was I with the index? so it

20:23

will still not use the index because

20:25

it's faster to just Scan

20:28

the whole table and keep the index

20:30

so you have to be careful with

20:32

it because depending on the type

20:34

of data and depending

20:37

on the Conditions as you are

20:39

put putting in the where clothes it may

20:41

decide to use the index in one situation and

20:44

not use any They've seen another situation

20:46

on a different type of record

20:48

sure so databases are tricky Yeah,

20:51

it always is and like I love the

20:53

one where they go, you know Greater

20:55

than January 1st and less than December 31st

20:57

is like, you know, you just left off

21:00

all the December 31st people Yeah, yeah,

21:02

so it's probably better to just write

21:04

less than one January of 2001 and

21:07

you Won't

21:09

be when you are now there. I

21:12

hate date math. I hate it Yeah,

21:15

I had a date math bug in one

21:17

of my in my websites

21:19

Just recently about listing gigs like

21:22

the day before the gig it disappeared from the

21:24

site. No He's just

21:26

a plus instead of a minus or less than instead

21:28

of a greater than Yeah, I

21:30

love it. Yeah, I know it's it's these

21:32

are the things we chase around So, you

21:34

know being able to see into the data

21:36

set and answer a bunch of

21:38

questions Did I write the link query wrong that

21:41

the actual output query? I

21:44

mean be incorrect or just badly perform it

21:46

like you know, you have a bunch of

21:48

choices on how to fix it You

21:51

could tweak the database Tweak

21:53

your expression you could switch over to a different

21:55

methodology, you know, like you have a bunch of

21:57

choices But you these you know, you

21:59

know, that's that's the

22:01

problem. Yeah. Yeah,

22:03

so this extension supports SQL Server

22:05

and Postgres, so you can use

22:08

the graphical query plan for both

22:10

of these databases. Mm-hmm, it's cool.

22:12

Yeah, you can see if there

22:15

are any table scans

22:17

which will make your queries

22:19

slow. Yeah. If it's using

22:21

indexes, is it doing an

22:23

index scan or index six

22:26

or things like that,

22:28

like reading and understanding query plans

22:30

is a separate science probably. So

22:33

instead of skills, although the documentation is better

22:35

than it ever was, it's easier now than

22:37

it used to be. It used

22:40

to be really you had to know

22:42

what query plan statements were like.

22:44

I can sense thousands of document

22:47

database users listening now going, sucks

22:50

to do you. Yeah, if

22:52

we don't have to write any for loops there, kiddo.

22:57

Sometimes you need to look for data

22:59

in your document store too, right? Yeah.

23:03

Now, if it was one right way to do this,

23:05

everybody would do it. The reality is there's a bunch

23:08

of ways to be successful and

23:10

there's a bunch of ways to get into trouble and

23:12

it's good to have tooling that'll help you go down

23:14

the right path. True. So I

23:16

know you said it before, it's just an extension,

23:19

but when you were explaining it,

23:21

how to use it, I was thinking what's the

23:23

name of it again. So can you

23:25

go over like one more time, just

23:27

how you would use this? It shows

23:29

up as an extension. Where

23:31

does it manifest itself in Visual

23:34

Studio? Yeah, you can. So once

23:36

you install it, you start debugging

23:38

your runs the bubble as

23:40

you would in any other case, you

23:43

hover over your I query variables.

23:45

So if the query and police

23:48

async, you can't use the query

23:50

plan, you need to have an

23:53

I queryable. And

23:55

when you hover your mouse on

23:57

the I queryable variable in in

24:00

Visual Studio, you will see

24:02

a query plan visualizer under

24:04

the magnifying glass that Visual

24:06

Studio shows whenever you are

24:08

trying to inspect variables. Yeah,

24:11

very cool. And when you

24:13

click on the query plan visualizer,

24:15

it will open a new window.

24:17

It will take a second or

24:20

two to actually fetch the plan

24:22

from the database and it

24:24

will render the plan in

24:26

an embedded WebView control. That's

24:29

very cool. If the database

24:31

connection is slow or the

24:33

query takes more

24:36

than five seconds, then it will

24:38

timeout because that's the hard timeout

24:40

limits by the Visual

24:43

Studio itself. So

24:45

that's one thing to keep in mind.

24:48

That's great. And also it's

24:51

not very helpful for queries

24:54

where you have first or

24:57

default async or count async

24:59

or similar queries which just

25:02

materialize the results

25:04

immediately and you don't

25:06

have an I queryable because for

25:09

such queries, you don't

25:11

have an I queryable variable.

25:14

So you could remove the

25:17

calls to the first and

25:19

count or sum and so on, but it's

25:22

not the same query now

25:24

because you are fetching the

25:26

call list and not just count or

25:28

sum. Right.

25:30

First item. So it's nice to be able

25:32

to debug what you're actually using rather than

25:34

have to appease the debugger just

25:36

to see it. That's cool. I

25:39

guess maybe it's time to take

25:41

a break when we come back

25:43

and we talk about the Entity

25:45

Framework Exceptions Library. Yeah, just one

25:47

thing that I wanted to add

25:49

is that this extension uses existing

25:51

libraries to render the SQL Server

25:53

and Postgres query plans. So I

25:56

wanted to say thanks to the authors

25:58

of those libraries. because without

26:01

those projects, I would not be

26:03

able to build this extension.

26:06

So thanks again for building

26:08

the SQL Server and Postgres

26:11

Visualizer libraries. Cool.

26:14

Excellent. Hold that thought, we'll

26:16

be right back after these very important messages. We're

26:23

back. It's.NET Rocks. I'm Carl Franklin.

26:26

That's Richard Campbell. Hey there.

26:28

Hey, and that is Georgi

26:31

Dalakishvili, and we're talking

26:33

about Entity Framework and what's going on

26:35

under the hood, and he's got

26:37

a great Visual Studio

26:39

extension that you can use to see what's going on

26:41

with your queries under the hood. And

26:44

I want to bring up this other, a couple

26:46

other actually really

26:48

great GitHub reposts

26:51

that you have. One is Entity

26:53

Framework Exceptions, and

26:56

the other is duckdb.net. But

26:58

let's talk about the extensions, exceptions

27:00

right now, in the Framework Exceptions

27:03

thing. Tell us about that. So

27:05

this is a library that started, I

27:09

guess most libraries start from

27:11

an internal usage because some

27:14

things that I found, copying

27:17

from one project to another, and I

27:19

decided to put up

27:21

it in a library and publish, thinking

27:24

that others would find it useful. So

27:27

what it does is, again, when using

27:29

Entity Framework, and let's

27:31

say you have a table with a unique

27:34

constraint, for example, an email

27:36

address is a unique constraint

27:38

in your table, and

27:40

you are inserting

27:44

customers or whatever in your

27:46

table, and if you

27:48

try to insert an record with

27:50

the same email, the database will

27:52

throw an error because the unique

27:54

constraint is violated, and when in

27:56

Entity Framework, this means that you

27:58

will get an... DB updates

28:01

exception in your C sharp code. That

28:03

thing with that is that DB update

28:05

exception doesn't really tell you whether this

28:07

was the unique constraint

28:09

violation or the safe, the

28:12

insert fails because of some other error

28:15

in the database. You need more detail.

28:17

So yeah. So you, you

28:19

have to check the inner exception of

28:21

this DB update exception. And

28:24

that's the innermost exception, the

28:26

deepest one. It's the

28:28

start of the whole thing, right? That's the

28:30

one that's all the way in is the

28:32

one was the original error and then it

28:34

cascades outwards. Yeah. That's that's yeah. Usually that's

28:36

the most interesting one, which gives you.

28:40

Lowest level. Thank you, frozen. Yeah. I think

28:42

I'm back now. Oh, he's back now. You're

28:44

back. So yeah, it's the, it's the lowest

28:46

level exception. The one that started at all.

28:49

Yeah. Yeah. So they say the lowest level

28:51

in the inner exception has, uh, if you

28:53

are, if it's sent a scroll server database,

28:55

send it to them as

28:57

QL exception. If it's a postgres

29:01

database, it will be NPG

29:03

or what, what it's called exception.

29:05

And depending on the,

29:07

on the database, you have to

29:09

check either the error number property

29:11

or the error code property or

29:14

whatever the library user

29:16

puts there. And,

29:18

and then again, depending on

29:20

the database, the error number

29:22

may be different between different,

29:24

uh, database engines. So, so if you

29:26

want to find the root cause,

29:28

and if you want to know that it's

29:30

a unique constraint violation, we have

29:33

to dig deeper and write

29:35

some, uh, database specific code

29:37

in your, uh, entity framework code. We

29:40

are, you are trying

29:42

to obstruct over the database

29:44

implementation. So, so what's

29:46

this library does is that it's,

29:49

it's, uh, adds an, uh, interceptor

29:52

to your, uh, entity framework,

29:54

uh, DB context. And

29:57

whenever there is an exception, uh,

30:00

generated from save changes

30:02

it will dig into the innermost

30:06

exception and translate

30:09

the database specific error

30:11

in the different

30:15

type of exception that is provided by

30:17

this library so you

30:19

can just change your code to

30:21

catch unique constraint exception and if

30:24

your code hits that catch block you now

30:26

know for sure that this

30:30

catch block was code

30:32

because this

30:34

catch block was hit because of the

30:37

unique constraint violation in the database and

30:40

even if you switch between databases and

30:43

if you go from SQL servers to

30:46

Oracle or MySQL or any

30:48

other major

30:51

databases your track edge

30:54

stays the same because the

30:56

database abstract because the

31:00

edit framework exceptions library

31:03

abstracts it along

31:05

between all the databases

31:08

so you don't have to update your code

31:10

and you

31:14

just put your

31:16

exception handling code in this

31:18

catch block. And the expectation is

31:21

that all the other stuff that's in

31:23

the exception handling block

31:25

is just residual of the initial

31:28

error. Yeah you can show

31:30

that this email address is already

31:32

in use or whatever you

31:35

need. So yes

31:37

apart from unique constraints

31:39

it handles other

31:42

cases like when you exceed the

31:45

length of the column for example

31:47

or you provide a null value

31:50

for the column that does not allow null and

31:52

so on you will get

31:55

a strongly typed exception in these

31:57

cases instead of generic

32:00

DB update exception. Right. OK. Should we

32:02

move on to DuckDB? First

32:04

of all, what is DuckDB? And

32:06

why do we need a duckdb.net for it?

32:10

Yeah, DuckDB is an embedded

32:12

database just like SQLite.

32:15

So you don't need to

32:18

set up any database

32:20

server or complex

32:23

configuration. No complex configuration is

32:25

needed. It's relatively

32:28

new, but it's becoming popular

32:30

in the data science work,

32:33

especially in the Python

32:36

community, I think, because Python

32:38

is frequently used for

32:41

data science and data

32:44

manipulation and things in these

32:46

types of projects. The

32:48

main difference between DuckDB and

32:51

SQLite is that SQLite

32:54

is transactional,

32:56

OETP-oriented database. And

32:59

DuckDB is all of the

33:02

database more geared for analytical

33:04

queries and analytical processing. Is

33:09

it all in memory, or can

33:11

it persist to disk too? Imagine

33:14

if it's in memory, it can persist. No,

33:16

it can persist to disk as well.

33:18

It has its own database format. It

33:21

can also run queries on

33:23

top of CSV files, which is very cool.

33:26

The old CSV

33:28

files suddenly become queryable.

33:31

It can also run queries

33:33

on top of pocket files.

33:37

And what's also cool

33:39

is that you can run queries on top

33:41

of JSON files. And you

33:43

can also run queries on files

33:45

that are stored in S3

33:48

buckets in AWS. So

33:50

you don't get

33:53

any interesting. If

33:56

you have a bucket file in S3,

33:59

you will just. fetch the part

34:01

of the file that is needed to answer your

34:04

queries by using the HTTP

34:06

range headers. How

34:08

do you pass the S3 credentials to it?

34:11

It has its own

34:14

secret stone I think where

34:16

you can sell your credentials

34:18

and for development

34:21

purposes, it can also

34:24

use your AWS credentials

34:26

from your home directory where

34:29

you store the token and the

34:31

all the temporary

34:34

credentials that you are using. That's cool.

34:39

So that's a short overview

34:41

of WTB. It's very fast

34:43

for analytical queries and

34:47

if you compare it to

34:49

pandas which is very popular

34:51

library in Python or OLLARS

34:54

which is also a popular

34:56

library for data manipulation in R.

35:03

It's fast I think. It's

35:06

very fast compared to them.

35:08

So duckdb.net is an ADO

35:10

provider for DuckDB? Yes. ado.net?

35:12

Yes, that's right. Wow. It's

35:14

an ado.net provider for DuckDB

35:17

and you can query DuckDB

35:23

databases, CSV files, Python

35:25

files basically whatever DuckDB

35:28

supports you can do it

35:30

from Ducknet. You

35:33

can also write to the

35:35

DuckDB tables and

35:38

DuckDB also has these very helpful

35:40

commands for exporting the

35:43

database to CSV or packet or whatever

35:45

file you need. So you can just

35:48

use this provider to write to DuckDB

35:50

and then it will shoot the copy

35:53

command and it will copy to CSV

35:55

or packet or whatever

35:58

format you specify. It's very

36:00

cool. So you

36:02

have your hands in a lot

36:04

of different technologies, which is kind

36:07

of typical of people that do

36:10

open source work and stuff these days. But

36:13

you also have

36:15

been doing open source and working on

36:17

other repos

36:20

as well, and especially as

36:23

you're a member of the .NET Foundation. Can

36:26

you tell me what's going on in the world

36:28

of open source as you see it right now?

36:30

What's hot? Well, I think

36:32

the usual problem, which is Microsoft

36:35

in most cases versus

36:38

the open

36:40

source community, well, maybe

36:43

not really versus, but

36:46

sometimes people expect Microsoft to provide all

36:48

the libraries they need. Sometimes

36:52

they are angry

36:54

when Microsoft comes, how is new library

36:56

because it kills some

36:58

existing open source library.

37:01

So the

37:03

usual complaints. And

37:08

the usual complaints from

37:10

users who expect support

37:13

for open source libraries.

37:16

How's your experience been with these?

37:19

I mean, you say in your notes

37:21

here that you received some sponsorship from

37:24

aws.net for duckdb.net.

37:26

That's pretty cool, huh? Yeah,

37:29

it happened this March. So

37:31

yeah, it's probably

37:33

useful library if AWS

37:35

thinks that it's worth

37:37

the sponsorship. Right. So

37:40

do they find that people are

37:42

using it on AWS in the

37:44

cloud? Actually, one of the members

37:46

of the community who picks these

37:48

libraries was asking on Twitter which

37:50

library to consider for sponsorship. And

37:53

I mentioned my

37:55

project. It

37:58

did not get picked. that

38:01

much but

38:03

after a couple of months later, I

38:05

got this surprise from AWS. So,

38:08

wow. Was that a one-time payment or is it

38:10

recurring? So, it's a recurring payment of $500 for

38:12

one year. So, 12 payments. Wow.

38:22

That's great. Congratulations. Thank

38:24

you. Yeah, you're not going to retire on that

38:26

but it's nice to at least get some compensation

38:28

for it. That's cool. But it also shows, like

38:30

you said, that it's an

38:33

indication that they find it valuable,

38:35

right? So, my question

38:37

is, why? Why

38:39

is this something that

38:41

AWS wants to put their

38:44

support behind? And not because it doesn't deserve

38:46

it, but do they find

38:48

that their customers are using it in the cloud?

38:51

Is DuckDV something

38:53

that you would use in the cloud as

38:55

opposed to, I don't know, a SQL Server

38:57

or Document Database to save time and money?

39:00

Well, it's not an

39:03

alternative for SQL Server

39:05

but it can be

39:09

useful. For example, just

39:12

recently, I needed to

39:14

use it in my

39:17

work project. So,

39:19

I'm a little bit biased, I guess.

39:23

But one of our

39:25

vendors is sending

39:27

a page of private files daily

39:31

when data changes on their

39:33

site which we need to import in

39:35

our database. So,

39:38

we set up an AWS

39:41

lambda which uses ftb under

39:43

the code to query the

39:45

private files from S3. So,

39:48

they upload the private files to

39:50

a shared S3

39:53

packet and see how

39:55

long that will run in response to

39:57

the new file and use ftb.net. to

40:01

read the private files and save

40:04

to address database. So

40:09

yeah, I think it can be useful in

40:12

different use cases. And

40:15

to be honest, I won't

40:17

be surprised if one day

40:19

AWS comes out with a

40:21

new service which uses .db under the hood. Because

40:25

they have done it frequently with

40:28

many different open source

40:31

projects like Elasticsearch and

40:33

Redis also I think if I'm not

40:36

mistaken and probably some other

40:38

services as well. Yeah, I know. Your numbers

40:40

are excellent. Clearly you're building stuff that helps

40:42

people when they're starring and

40:44

forking and contributing. So that's pretty

40:46

cool. Yeah. Do you

40:48

get a lot of contributors in some of these projects?

40:51

I was just going to take a peek and think

40:53

because yeah, you kind of do that one on your

40:55

own, the visualizer. They're

40:58

fairly unique products. Take a look.

41:01

Yes. So for .db.net,

41:06

I think it's the project with most contributors.

41:10

I'm not sure in terms of source

41:14

code how is it. But

41:18

I think it's at like 10 or 15 contributors.

41:22

Some of the contributions may be small

41:24

but I remember one

41:27

or two contributors who contributed

41:29

quite a significant

41:31

portion of the code. And

41:34

it definitely helps because when you are

41:37

coding alone all the

41:39

time, these projects can become boring

41:41

sometimes or you can become tired

41:44

which is obvious.

41:47

And it's nice to have

41:49

a person who helps

41:52

even from time to time because

41:54

we get tired also. Sure.

41:58

Yeah, sure. You can't write the same code. code

42:00

every day, you know, gets old after a while.

42:02

Yeah, indeed. Yeah, that's probably

42:05

one more reason why the

42:08

sponsorship is important because it's

42:12

a show. It's a way to

42:14

say thank you to the person who

42:17

spends their own time working

42:20

on something for me. Is there anything that

42:22

we missed in these projects

42:24

that you'd want to mention before

42:26

we move on? Yeah, the

42:29

entity framework subscription is

42:31

the most popular project, open

42:34

source project that I have with

42:36

more than a million downloads

42:39

in case of SQL Server and Postgres

42:42

and more than 1000 stars

42:44

on GitHub. Wow. That's

42:47

great. And what's your role at that foundation?

42:49

Do you sit on some committees? No,

42:52

no, I just wanted to join

42:55

and see. Oh, you're a member. Yeah,

42:58

what was going on there? You

43:01

sound like someone who's, you know, doing a serious work

43:03

in open source so you could have some good conversations

43:05

there too. They're all trying to get

43:07

better. Yeah. Well, what are

43:09

you working on now? Or is there anything new that

43:12

you can share? It's

43:14

the more features

43:17

to the wdb.net

43:19

library. They

43:23

have support for

43:25

composite types like lists and

43:28

charts and maps

43:30

and so on. So

43:33

currently you can't insert those

43:35

data types from .net

43:38

and working towards making it

43:40

possible to write those

43:43

types to the database from the .net

43:45

code. Nice. Sounds like you're

43:47

a busy guy. Is it that you must

43:50

really love doing this? Do you

43:52

have a life outside of programming or is this

43:54

like your 24-7 obsession? A

43:57

lot of projects. Yeah.

44:00

Yes, there is

44:02

Joe Cohen. So,

44:04

software engineer goes on vacation and

44:06

finally I can work on my

44:08

projects now. Right. Yeah,

44:14

all right. So, it's been great

44:16

talking to you and wow, what a gem.

44:19

This is great stuff. They believe it's been

44:21

some time in these repositories to take a

44:23

look around. It's tools that can help you.

44:25

Absolutely. Yeah, so one thing

44:28

that I think I found

44:30

on Twitter, a couple of weeks

44:32

ago, I always thought towards

44:34

the discussion was why do

44:38

software engineers write codes

44:40

of their work and why

44:43

do they spend their time

44:46

on something that

44:49

they do as a job. And one

44:51

of the comments was that engineers

44:54

like solving problems, they

44:56

enjoy it and they

44:58

work on, they

45:01

spend time after the

45:03

work because they enjoy solving the problems

45:05

so they are just doing whatever they

45:07

enjoy. So, I

45:10

think that's a good explanation. Fair

45:12

enough. Yeah, it's true. Let it

45:14

out. Absolutely true. And yeah, sometimes

45:16

I find it hard

45:18

to explain to other people like why are

45:20

you doing it and I

45:22

just enjoy it. Yeah,

45:25

I do too. And I do it for fun. You

45:28

know, when I'm looking around and saying, you know,

45:30

I got a little lull here. Shall

45:33

I like learn some new music

45:35

on guitar? Yeah. No,

45:37

I'm going to write some code. Yeah,

45:41

for sure. All right, Georgie. Great

45:43

talking to you and thanks for sharing the

45:45

time with us. Thanks again. All right. And

45:47

we'll talk to you next time on

45:49

Dotnet. dotnet

46:11

ross is brought to you by Franklin's

46:13

Net and produced by Plop Studios, a

46:16

full-service audio, video and post-production facility

46:19

located physically in New London, Connecticut

46:21

and of course in the cloud.

46:27

Visit our website at dotnetrocks.com

46:29

for RSS feeds, downloads, mobile

46:32

apps, comments and access to

46:34

the full archives going back

46:36

to show number one recorded

46:39

in September 2002. Make

46:42

sure you check out our sponsors, they keep us

46:44

in business. Now go write some code,

46:47

see you next time.