C#, Dapper & SQL: Interview Questions With Answers

Check out Dometrain and use code ZORAN for 15% off any course ► dometrain.com/?coupon_code=ZORAN
Download source code ► / zoranhorvat
Join Discord server with topics on C# ► codinghelmet.com/go/discord
Enroll course Beginning Object-Oriented Programming with C# ► codinghelmet.com/go/beginning...
Subscribe ► / @zoran-horvat
Try your skills on this set of three interview questions involving a relational database - two tables and a many-to-many relationship - and a small application.
Design queries on this system of tables and materialize the data into domain objects. Make sure to avoid typical pitfalls in querying and, especially, in mapping the relational data to objects, and you'll be there!
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
👨 About Me 👨
Hi, I’m Zoran, I have more than 20 years of experience as a software developer, architect, team lead, and more. I have been programming in C# since its inception in the early 2000s. Since 2017 I have started publishing professional video courses at Pluralsight and Udemy and by this point, there are over 100 hours of the highest-quality videos you can watch on those platforms. On my KZbin channel, you can find shorter video forms focused on clarifying practical issues in coding, design, and architecture of .NET applications.❤️
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
⚡️COPYRIGHT NOTICE:
The Copyright Laws of the United States recognize a “fair use” of copyrighted content. Section 107 of the U.S. Copyright Act states: “Notwithstanding the provisions of sections 106 and 106A, the fair use of a copyrighted work, including such use by reproduction in copies or phono records or by any other means specified by that section, for purposes such as criticism, comment, news reporting, teaching (including multiple copies for classroom use), scholarship, or research, is not an infringement of copyright." This video and our youtube channel, in general, may contain certain copyrighted works that were not specifically authorized to be used by the copyright holder(s), but which we believe in good faith are protected by federal law and the Fair use doctrine for one or more of the reasons noted above.
#codinginterview #csharp #dotnet

[@zoran-horvat]

Check out Dometrain and use code ZORAN for 15% off any course ► dometrain.com/?coupon_code=ZORAN

[@adrianspikes6454]

Great questions! I ran into this issue years ago with land owners and leases. Every owner had it's own row to assets but display needed to be concise and professional, just as you have demonstrated here. I have a similar issue with a new project. I prefer not using enums for a my angular UI, like with my Blazor and razor UIs for states/counties and instead using a strategy similar to your video.

[@zoran-horvat]

These three questions you will find in every project working with a relational database. That is also why I prefer ORMs in OOP code - all the questions produce one line of code each, and everything I had to develop manually would still be there, free of charge.

[@alfonsdeda8912]

Hi Zoran, great video! Can you explain the part of order by book authors.Ordinal please? What is the difference between ordering by Id?

[@zoran-horvat]

The original position of an author might change later, e.g. after swapping two authors. In that case, the ordinal sort order would not follow the ID sort order anymore. Another use case comes to mind: inserting authors out of order. Their IDs would grow, but their ordinals would look random. We must use explicit Ordinal column as a user-managed sort order of items.

[@alexlo5655]

Hi Zoran, Thank you for the Video. Could you mention what AI will generate for you?

[@zoran-horvat]

In response to these questions? I think it made a few mistakes in SQL and got large parts of C# wrong, primarily grouping and unifying objects. It was totally confused by Dapper's multi-mapping. But it did write CREATE TABLE statements and the entire seeding script (in the third attempt), saving me some 15 minutes of time in one go. I was really glad about that. Overall impression is that it helped with small things, but I had to manage the hard parts.

[@AkosLukacs42]

So the ad means you will publish a course on Dometrain?

[@zoran-horvat]

I don't have such plans at the moment, but I accepted to advertise courses from other authors because I know their work, I know some of them in person, too, and I genuinely believe it is a good place for programmers to learn.

[@alexlo5655]

Should the code be asynchronous and use QueryAsync?

[@zoran-horvat]

Yes, you should always look for async methods when doing I/O such as database access.

[@arghasen6535]

If I have to use raw sql query , I would have used the stored procedure and in c# side just returning the models

[@zoran-horvat]

Well, you would not use C# then. What would be the point of having a domain model in such a versatile language if the stores procedures do it all? Speaking of which, you would also quickly find that you have lost dynamic resolution of behavior in the process. In no time, you would be back in the 1980s, but with the problems of 2020s to solve. And you would be the only member of the team.

[@ibrahimhussain3248]

@@zoran-horvat FYI... I love your way of C# coding. Stored procedures are backwards? :) I think you might have misunderstood what he really meant. Your raw SQL was in a variable. You could put the EXACT same SQL with parameters in an sp without changing your domain model at all. The SQL can be in an SSDT project with syntax highlighting, error checking and migrations. Give the client user only EXECUTE access. No need to be the only person in the team :)

[@ibrahimhussain3248]

@@zoran-horvat Also, regarding your statement, '...I can also pull the data dynamically and save database resources...': This claim is false because the dataset returned by your query remains unchanged, regardless of the values passed to the TakeWhile clause. The executed SQL statement and its parameters do not change unless you modify the maxTitleLength parameter value.

[@zoran-horvat]

@@ibrahimhussain3248 I think you didn't understand the TakeWhile part.

[@ibrahimhussain3248]

@@zoran-horvat Do you mean to say that the lambda passed into TakeWhile changes the dataset returned from database while the parameter @maxTitleLength is constant? If not, then it does not save database resources. I actually wrote all the code and tested with sql server profiler. The filtering is done after fetching the complete dataset from database. Can I upload the code to github and share for you to have a quick glance and tell me what's wrong. I see this as a chance to learn.