-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathparameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite.html
18 lines (14 loc) · 31.6 KB
/
parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<!DOCTYPE html><html lang=en prefix="og: https://ogp.me/ns#"><head><meta charset=utf-8><base href=https://blog.georgekosmidis.net/parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite.html><meta name=viewport content="width=device-width, initial-scale=1"><meta name=description content="Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. This can be useful, because raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model, but it could also be a problem when it comes down to Unit Testing ."><meta name=author content="George Kosmidis"><meta http-equiv=Content-Security-Policy content="script-src 'nonce-2f6efdd7-9a9b-4d86-b956-69985ba92a39'"><link rel=me type=text/html href=https://github.com/georgekosmidis><link rel=me type=text/html href="https://www.linkedin.com/in/georgekosmidis/"><link rel=me type=text/html href=https://www.youtube.com/c/GeorgeKosmidis><link rel=me type=text/html href="https://sessionize.com/georgekosmidis/"><link rel=me type=text/html href="https://www.meetup.com/munich-dotnet-meetup/members/202480733/profile/"><link rel=me type=text/html href="https://georgekosmidis.net/"><link rel=icon type=image/jpg href=/media/me_180x180.jpg><link rel=apple-touch-icon type=image/jpg sizes=180x180 href=/media/me_180x180.jpg><meta property=og:title content="Parameterized Raw SQL Queries with Entity Framework: How to Unit Test with SQLite"><meta property=og:type content=article><meta property=og:url content=https://blog.georgekosmidis.net/parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite.html><meta property="og:description " content="Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. This can be useful, because raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model, but it could also be a problem when it comes down to Unit Testing ."><meta property=og:article:published_time content="04/07/2018 11:03:06"><meta property=og:article:modified_time content="07/26/2020 09:44:44"><meta property=og:article:expiration_time content="12/31/9999 23:59:59"><meta property=og:article:author content="George Kosmidis"><meta property=og:article:section content=""><meta property=og:article:tag content=" ASP.NET Core, Entity Framework, Parameterized Queries, RawSQL, SQLite, Unit Testing, Web API"><link href=https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css rel=stylesheet integrity=sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3 crossorigin=anonymous><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/cookieconsent@3.1.1/build/cookieconsent.min.css integrity="sha256-zQ0LblD/Af8vOppw18+2anxsuaz3pWYyVWi+bTvTH8Q=" crossorigin=anonymous><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/@highlightjs/cdn-assets@11.4.0/styles/vs2015.min.css integrity="sha256-Pi771++jBrwgeHVYGOa1sjN8idXlrrYSKQVI7+JA54k=" crossorigin=anonymous><title>Parameterized Raw SQL Queries with Entity Framework: How to Unit Test with SQLite</title><style>header{background-image:url(/media/header_bg.jpg);background-size:cover;background-position:100% 100%}header svg{width:25px;fill:#f8f9fa}footer svg{width:25px;fill:black}.me{width:180px;height:180px}.article{overflow-wrap:break-word;word-wrap:break-word;-ms-word-break:break-all;word-break:break-all;word-break:break-word}.article img{max-width:100%;height:auto}h1,h2,h3,h4,h5,h6{font-weight:400}h2{margin-top:4rem}.right-column-container h2,.index-card-wrapper h2{margin-top:0}.h3,h3{margin-top:3rem;font-size:1.65rem}.h4,h4{font-size:1.4eem}.h5,h5{font-size:1.2eem}.h4,.h5,h4,h5{margin-top:2rem}th,strong,b{font-weight:500}a{text-decoration:none}a:hover{text-decoration:underline}a.btn svg{margin-bottom:3px}.article-google-engine-top,.article-google-engine-left{display:none}@media (min-width:992px){.container{max-width:1200px}header > .container{max-width:768px}.article-google-engine-left{display:block}td{padding:10px}}@media (max-width:991.98px){.container{padding-right:calc(var(--bs-gutter-x) * .3);padding-left:calc(var(--bs-gutter-x) * .3)}.article-google-engine-top{display:block}td{padding:5px 2px 5px 2px}}blockquote{text-rendering:optimizelegibility;font-weight:400;line-height:160%;box-sizing:inherit;background-color:#DEEBFF;outline-color:#171717;color:#171717;word-wrap:break-word;word-break:break-word;border:1px solid #DEEBFF;border-radius:.375rem;margin-top:1rem;padding:1rem;font-size:1rem;transition:height .5s ease-in,opacity .5s ease-in;display:block;position:relative}blockquote p{margin-bottom:0}dfn{border-color:#343a40}</style><script async src="https://www.googletagmanager.com/gtag/js?id=UA-3071108-41" nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39></script><script nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39>window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments);}
gtag('js',new Date());gtag('config','UA-3071108-41');</script></head><body class=bg-light><header class="container-fluid p-3 bg-dark border-bottom"> <div class="container text-white"> <div class=row> <div class="col-sm-12 col-md-3 text-center"> <a href="/"> <img src=/media/me_180x180.jpg class="rounded-circle border border-white border-5 me" alt="George Kosmidis - Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. This can be useful, because raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model, but it could also be a problem when it comes down to Unit Testing ."> </a> </div> <div class="col-sm-12 col-md-9"> <div class=container> <div class=row> <div class=col> <h1 class="display-5 text-center">George Kosmidis</h1> <small class="d-block text-center mt-3">Microsoft MVP | Speaks of Azure, AI & .NET | Founder of Munich .NET <br> Building tomorrow @<div style=display:none>slalom</div> <img src=/media/slalom_small.png title=slalom alt=slalom></small> </div> </div> <div class="row mt-3"> <div class="col text-center"> <a href=https://github.com/georgekosmidis target=_blank class=btn rel=noopener> <svg role=img class=icon viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>GitHub</title><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg> </a> <a href="https://www.linkedin.com/in/georgekosmidis/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>LinkedIn</title><path d="M20.447 20.452h-3.554v-5.569c0-1.328-.027-3.037-1.852-3.037-1.853 0-2.136 1.445-2.136 2.939v5.667H9.351V9h3.414v1.561h.046c.477-.9 1.637-1.85 3.37-1.85 3.601 0 4.267 2.37 4.267 5.455v6.286zM5.337 7.433c-1.144 0-2.063-.926-2.063-2.065 0-1.138.92-2.063 2.063-2.063 1.14 0 2.064.925 2.064 2.063 0 1.139-.925 2.065-2.064 2.065zm1.782 13.019H3.555V9h3.564v11.452zM22.225 0H1.771C.792 0 0 .774 0 1.729v20.542C0 23.227.792 24 1.771 24h20.451C23.2 24 24 23.227 24 22.271V1.729C24 .774 23.2 0 22.222 0h.003z"></path></svg> </a> <a href=https://www.youtube.com/c/GeorgeKosmidis target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>YouTube</title><path d="M23.498 6.186a3.016 3.016 0 0 0-2.122-2.136C19.505 3.545 12 3.545 12 3.545s-7.505 0-9.377.505A3.017 3.017 0 0 0 .502 6.186C0 8.07 0 12 0 12s0 3.93.502 5.814a3.016 3.016 0 0 0 2.122 2.136c1.871.505 9.376.505 9.376.505s7.505 0 9.377-.505a3.015 3.015 0 0 0 2.122-2.136C24 15.93 24 12 24 12s0-3.93-.502-5.814zM9.545 15.568V8.432L15.818 12l-6.273 3.568z"></path></svg> </a> <a href="https://sessionize.com/georgekosmidis/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 288 288" xmlns=http://www.w3.org/2000/svg><title>Sessionize</title><g transform="translate(0.000000,288.000000) scale(0.100000,-0.100000)"><path d="M135 2861 c-22 -10 -54 -34 -72 -52 -67 -71 -63 22 -63 -1369 0 -1391 -4 -1298 63 -1369 70 -73 42 -71 715 -71 l602 0 0 40 c0 108 -52 232 -127 301 -92 85 -229 125 -469 139 -119 6 -160 13 -190 28 -63 32 -96 81 -108 161 -6 43 12 87 470 1118 263 590 480 1073 483 1073 3 0 22 -42 41 -92 47 -127 157 -344 228 -453 156 -239 367 -450 607 -608 100 -65 373 -202 478 -238 37 -13 67 -27 67 -30 0 -3 -348 -160 -772 -349 -425 -188 -777 -346 -782 -350 -4 -5 11 -18 35 -30 235 -119 367 -331 394 -633 l7 -77 482 0 c444 0 484 2 521 19 51 23 111 89 125 138 8 27 10 249 8 773 -4 660 -6 743 -22 809 -69 289 -197 522 -396 721 -199 199 -432 327 -721 396 -67 16 -148 18 -819 21 -712 3 -747 2 -785 -16z m2069 -483 c99 -51 198 -181 200 -264 1 -57 -21 -79 -77 -78 -137 3 -328 220 -288 328 19 50 84 55 165 14z"></path></g></svg> </a> <a href="https://www.meetup.com/munich-dotnet-meetup/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>Meetup</title><path d="M6.98.555a.518.518 0 0 0-.105.011.53.53 0 1 0 .222 1.04.533.533 0 0 0 .409-.633.531.531 0 0 0-.526-.418zm6.455.638a.984.984 0 0 0-.514.143.99.99 0 1 0 1.02 1.699.99.99 0 0 0 .34-1.36.992.992 0 0 0-.846-.482zm-3.03 2.236a5.029 5.029 0 0 0-4.668 3.248 3.33 3.33 0 0 0-1.46.551 3.374 3.374 0 0 0-.94 4.562 3.634 3.634 0 0 0-.605 4.649 3.603 3.603 0 0 0 2.465 1.597c.018.732.238 1.466.686 2.114a3.9 3.9 0 0 0 5.423.992c.068-.047.12-.106.184-.157.987.881 2.47 1.026 3.607.24a2.91 2.91 0 0 0 1.162-1.69 4.238 4.238 0 0 0 2.584-.739 4.274 4.274 0 0 0 1.19-5.789 2.466 2.466 0 0 0 .433-3.308 2.448 2.448 0 0 0-1.316-.934 4.436 4.436 0 0 0-.776-2.873 4.467 4.467 0 0 0-5.195-1.656 5.106 5.106 0 0 0-2.773-.807zm-5.603.817a.759.759 0 0 0-.423.135.758.758 0 1 0 .863 1.248.757.757 0 0 0 .193-1.055.758.758 0 0 0-.633-.328zm15.994 2.37a.842.842 0 0 0-.47.151.845.845 0 1 0 1.175.215.845.845 0 0 0-.705-.365zm-8.15 1.028c.063 0 .124.005.182.014a.901.901 0 0 1 .45.187c.169.134.273.241.432.393.24.227.414.089.534.02.208-.122.369-.219.984-.208.633.011 1.363.237 1.514 1.317.168 1.199-1.966 4.289-1.817 5.722.106 1.01 1.815.299 1.96 1.22.186 1.198-2.136.753-2.667.493-.832-.408-1.337-1.34-1.12-2.26.16-.688 1.7-3.498 1.757-3.93.059-.44-.177-.476-.324-.484-.19-.01-.34.081-.526.362-.169.255-2.082 4.085-2.248 4.398-.296.56-.67.694-1.044.674-.548-.029-.798-.32-.72-.848.047-.31 1.26-3.049 1.323-3.476.039-.265-.013-.546-.275-.68-.263-.135-.572.07-.664.227-.128.215-1.848 4.706-2.032 5.038-.316.576-.65.76-1.152.784-1.186.056-2.065-.92-1.678-2.116.173-.532 1.316-4.571 1.895-5.599.389-.69 1.468-1.216 2.217-.892.387.167.925.437 1.084.507.366.163.759-.277.913-.412.155-.134.302-.276.49-.357.142-.06.343-.095.532-.094zm10.88 2.057a.468.468 0 0 0-.093.011.467.467 0 0 0-.36.555.47.47 0 0 0 .557.36.47.47 0 0 0 .36-.557.47.47 0 0 0-.464-.37zm-22.518.81a.997.997 0 0 0-.832.434 1 1 0 1 0 1.39-.258 1 1 0 0 0-.558-.176zm21.294 2.094a.635.635 0 0 0-.127.013.627.627 0 0 0-.48.746.628.628 0 0 0 .746.483.628.628 0 0 0 .482-.746.63.63 0 0 0-.621-.496zm-18.24 6.097a.453.453 0 0 0-.092.012.464.464 0 1 0 .195.908.464.464 0 0 0 .356-.553.465.465 0 0 0-.459-.367zm13.675 1.55a1.044 1.044 0 0 0-.583.187 1.047 1.047 0 1 0 1.456.265 1.044 1.044 0 0 0-.873-.451zM11.4 22.154a.643.643 0 0 0-.36.115.646.646 0 0 0-.164.899.646.646 0 0 0 .899.164.646.646 0 0 0 .164-.898.646.646 0 0 0-.54-.28z"></path></svg> </a> </div> </div> </div> </div> </div> </div> </header> <main class="container bg-light pt-4 pb-5"> <div class="row masonry"> <div class=container> <div class="row mx-auto article-google-engine-top"> <div class="col pb-4"> <div class="card text-center bg-transparent border-0" style=height:33px><script async src="https://cse.google.com/cse.js?cx=c67a1214306f44e87" nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39></script><div class=gcse-searchbox-only></div> </div> </div> </div> <div class="row mx-auto"> <div class="col-lg-8 mb-4 shadow bg-white rounded"> <div class="container article"> <div class="row border-end border-start border-bottom bg-light"> <div class=col style=text-align:center> <a class="btn align-middle" href=https://github.com/georgekosmidis/blog.georgekosmidis.net//tree/main/workables/articles/100180-parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite/content.html role=button style=text-decoration:none target=_blank rel=noopener> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 fill=currentColor viewBox="0 0 16 16"><path d="M8 0C3.58 0 0 3.58 0 8c0 3.54 2.29 6.53 5.47 7.59.4.07.55-.17.55-.38 0-.19-.01-.82-.01-1.49-2.01.37-2.53-.49-2.69-.94-.09-.23-.48-.94-.82-1.13-.28-.15-.68-.52-.01-.53.63-.01 1.08.58 1.23.82.72 1.21 1.87.87 2.33.66.07-.52.28-.87.51-1.07-1.78-.2-3.64-.89-3.64-3.95 0-.87.31-1.59.82-2.15-.08-.2-.36-1.02.08-2.12 0 0 .67-.21 2.2.82.64-.18 1.32-.27 2-.27.68 0 1.36.09 2 .27 1.53-1.04 2.2-.82 2.2-.82.44 1.1.16 1.92.08 2.12.51.56.82 1.27.82 2.15 0 3.07-1.87 3.75-3.65 3.95.29.25.54.73.54 1.48 0 1.07-.01 1.93-.01 2.2 0 .21.15.46.55.38A8.012 8.012 0 0 0 16 8c0-4.42-3.58-8-8-8z" /></svg> Edit Page </a> <a class="btn align-middle" href="https://github.com/georgekosmidis/blog.georgekosmidis.net//issues/new?permalink=https://github.com/georgekosmidis/blog.georgekosmidis.net//tree/main/workables/articles/100180-parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite/content.html" role=button style=text-decoration:none target=_blank rel=noopener> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 fill=currentColor viewBox="0 0 24 24"><path fill-rule=evenodd d="M2.5 12a9.5 9.5 0 1119 0 9.5 9.5 0 01-19 0zM12 1C5.925 1 1 5.925 1 12s4.925 11 11 11 11-4.925 11-11S18.075 1 12 1zm0 13a2 2 0 100-4 2 2 0 000 4z"></path></svg> Create Issue </a> <a class="btn align-middle" href="https://github.com/georgekosmidis/blog.georgekosmidis.net/discussions/new?title=Parameterized Raw SQL Queries with Entity Framework: How to Unit Test with SQLite&body=Article%20URL:%20https://blog.georgekosmidis.net//parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite.html" role=button style=text-decoration:none target=_blank rel=noopener> <svg aria-hidden=true height=22 viewBox="0 0 16 16" width=22><path fill-rule=evenodd d="M1.5 2.75a.25.25 0 01.25-.25h8.5a.25.25 0 01.25.25v5.5a.25.25 0 01-.25.25h-3.5a.75.75 0 00-.53.22L3.5 11.44V9.25a.75.75 0 00-.75-.75h-1a.25.25 0 01-.25-.25v-5.5zM1.75 1A1.75 1.75 0 000 2.75v5.5C0 9.216.784 10 1.75 10H2v1.543a1.457 1.457 0 002.487 1.03L7.061 10h3.189A1.75 1.75 0 0012 8.25v-5.5A1.75 1.75 0 0010.25 1h-8.5zM14.5 4.75a.25.25 0 00-.25-.25h-.5a.75.75 0 110-1.5h.5c.966 0 1.75.784 1.75 1.75v5.5A1.75 1.75 0 0114.25 12H14v1.543a1.457 1.457 0 01-2.487 1.03L9.22 12.28a.75.75 0 111.06-1.06l2.22 2.22v-2.19a.75.75 0 01.75-.75h1a.25.25 0 00.25-.25v-5.5z"></path></svg> Discuss </a> <a class="btn ms-4" href="/" role=button style=text-decoration:none> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 class=mb-1 fill=currentColor class="bi bi-house-fill" viewBox="0 0 16 16"><path fill-rule=evenodd d="m8 3.293 6 6V13.5a1.5 1.5 0 0 1-1.5 1.5h-9A1.5 1.5 0 0 1 2 13.5V9.293l6-6zm5-.793V6l-2-2V2.5a.5.5 0 0 1 .5-.5h1a.5.5 0 0 1 .5.5z"></path><path fill-rule=evenodd d="M7.293 1.5a1 1 0 0 1 1.414 0l6.647 6.646a.5.5 0 0 1-.708.708L8 2.207 1.354 8.854a.5.5 0 1 1-.708-.708L7.293 1.5z"></path></svg> Go Home </a> </div> </div> <div class=row> <div class="col mt-3"> <h1 class="text-center display-6">Parameterized Raw SQL Queries with Entity Framework: How to Unit Test with SQLite</h1> </div> </div> <div class=row> <div class="col pb-2"> <div class="text-black-50 text-center"> <small>by <a href="https://georgekosmidis.net/" rel=noopener>George Kosmidis</a> / Published 7 years ago, modified 5 years ago</small> </div> </div> </div> <div class=row> <div class="col pt-3 pb-6"> <p>Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. This can be useful, because raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model, but it could also be a problem when it comes down to <em>Unit Testing</em>.</p> <p> </p> <blockquote><p> You can read more about Raw SQL Queries here: <a href="https://docs.microsoft.com/en-us/ef/core/querying/raw-sql?WT.mc_id=DT-MVP-5004591" target=_blank rel="noopener noreferrer">https://docs.microsoft.com/en-us/ef/core/querying/raw-sql</a> </p></blockquote> <p>By using raw SQL queries though, in any case, it is important to remember to parameterize user input to protect your system from SQL injection attacks.</p> <blockquote><p> Read more about SQL Injections in this <a href="https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-injection?view=sql-server-2017&WT.mc_id=DT-MVP-5004591" target=_blank rel="noopener noreferrer">Microsoft article</a> and in this <a href=https://www.owasp.org/index.php/SQL_Injection target=_blank rel="noopener noreferrer">OWASP page</a></p> </blockquote> <p>For example, the following gist file contains a basic raw SQL Query that is using user input the wrong way, and thus, it’s vulnerable to SQL Injection attacks:<br> <pre><code class=language-csharp>var username = "george";//coming from UI
var blogs = context.Blogs
.FromSql("SELECT * FROM dbo.Blogs WHERE username = '" +username+"'") // <- vulnerable to sql injection attacks
.ToList();</code></pre></p> <p>To solve this, Microsoft suggests one of the 3 following approaches:</p> <h2>Using SqlParameter</h2> <p><pre><code class=language-csharp>var username = new SqlParameter("username ", "george");
var blogs = context.Blogs
.FromSql("SELECT * FROM dbo.Blogs WHERE username = @username", username )
.ToList();</code></pre></p> <p>This of course works perfectly against injection attacks, but if we try to unit test the above code using SQLite, the test will fail with the error <code>Message: System.InvalidCastException : Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'Microsoft.Data.Sqlite.SqliteParameter'.</code><br>which is self explanatory! An <code>SqlClient.SqlParmeter</code> is not an <code>Sqlite.SqliteParameter</code>!</p> <h2>Using <code>String.Format</code> syntax</h2> <p><pre><code class=language-csharp>var username = "george";
var blogs = context.Blogs
.FromSql("SELECT * FROM dbo.Blogs WHERE username = {0}", username )
.ToList();</code></pre></p> <p>Although this may look like <code>String.Format</code> syntax, the supplied value is wrapped in a parameter and the generated parameter name is inserted where the {0} placeholder was specified.</p> <h2>Using interpolation syntax</h2> <p><pre><code class=language-csharp>var username = "george";
var blogs = context.Blogs
.FromSql(@"SELECT * FROM dbo.Blogs WHERE username = {username}")
.ToList();</code></pre></p> <p>Same as the previous approach, the supplied value is wrapped in a parameter and the generated parameter name inserted where the {0} placeholder was specified.</p> <p>The last two approaches, actually allow .NET to wrap the supplied value with the appropriate parameter for each dbContext: <code>SqlClient.SqlParameter</code> for your running app, <code>Sqlite.SqliteParameter</code> for your tests!</p> </div> </div> <div class="row border-end border-start border-top bg-light mt-4"> <div class="col pt-2" style=text-align:center> This page is <strong>open source</strong>. Noticed a typo? Or something unclear?<br> <a class="btn align-middle" href=https://github.com/georgekosmidis/blog.georgekosmidis.net//tree/main/workables/articles/100180-parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite/content.html role=button style=text-decoration:none target=_blank rel=noopener> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 fill=currentColor viewBox="0 0 16 16"><path d="M8 0C3.58 0 0 3.58 0 8c0 3.54 2.29 6.53 5.47 7.59.4.07.55-.17.55-.38 0-.19-.01-.82-.01-1.49-2.01.37-2.53-.49-2.69-.94-.09-.23-.48-.94-.82-1.13-.28-.15-.68-.52-.01-.53.63-.01 1.08.58 1.23.82.72 1.21 1.87.87 2.33.66.07-.52.28-.87.51-1.07-1.78-.2-3.64-.89-3.64-3.95 0-.87.31-1.59.82-2.15-.08-.2-.36-1.02.08-2.12 0 0 .67-.21 2.2.82.64-.18 1.32-.27 2-.27.68 0 1.36.09 2 .27 1.53-1.04 2.2-.82 2.2-.82.44 1.1.16 1.92.08 2.12.51.56.82 1.27.82 2.15 0 3.07-1.87 3.75-3.65 3.95.29.25.54.73.54 1.48 0 1.07-.01 1.93-.01 2.2 0 .21.15.46.55.38A8.012 8.012 0 0 0 16 8c0-4.42-3.58-8-8-8z" /></svg> Edit Page </a> <a class="btn align-middle" href="https://github.com/georgekosmidis/blog.georgekosmidis.net//issues/new?permalink=https://github.com/georgekosmidis/blog.georgekosmidis.net//tree/main/workables/articles/100180-parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite/content.html" role=button style=text-decoration:none target=_blank rel=noopener> <svg xmlns=http://www.w3.org/2000/svg width=22 height=22 fill=currentColor viewBox="0 0 24 24"><path fill-rule=evenodd d="M2.5 12a9.5 9.5 0 1119 0 9.5 9.5 0 01-19 0zM12 1C5.925 1 1 5.925 1 12s4.925 11 11 11 11-4.925 11-11S18.075 1 12 1zm0 13a2 2 0 100-4 2 2 0 000 4z"></path></svg> Create Issue </a> <a class="btn align-middle" href="https://github.com/georgekosmidis/blog.georgekosmidis.net/discussions/new?title=Parameterized Raw SQL Queries with Entity Framework: How to Unit Test with SQLite&body=Article%20URL:%20https://blog.georgekosmidis.net//parameterized-raw-sql-queries-with-entity-framework-how-to-unit-test-with-sqlite.html" role=button style=text-decoration:none target=_blank rel=noopener> <svg aria-hidden=true height=22 viewBox="0 0 16 16" version=1.1 width=22 data-view-component=true class="octicon octicon-comment-discussion UnderlineNav-octicon d-none d-sm-inline"><path fill-rule=evenodd d="M1.5 2.75a.25.25 0 01.25-.25h8.5a.25.25 0 01.25.25v5.5a.25.25 0 01-.25.25h-3.5a.75.75 0 00-.53.22L3.5 11.44V9.25a.75.75 0 00-.75-.75h-1a.25.25 0 01-.25-.25v-5.5zM1.75 1A1.75 1.75 0 000 2.75v5.5C0 9.216.784 10 1.75 10H2v1.543a1.457 1.457 0 002.487 1.03L7.061 10h3.189A1.75 1.75 0 0012 8.25v-5.5A1.75 1.75 0 0010.25 1h-8.5zM14.5 4.75a.25.25 0 00-.25-.25h-.5a.75.75 0 110-1.5h.5c.966 0 1.75.784 1.75 1.75v5.5A1.75 1.75 0 0114.25 12H14v1.543a1.457 1.457 0 01-2.487 1.03L9.22 12.28a.75.75 0 111.06-1.06l2.22 2.22v-2.19a.75.75 0 01.75-.75h1a.25.25 0 00.25-.25v-5.5z"></path></svg> Discuss </a> </div> </div> </div> </div> <div class=col-lg-4> <div class="container px-0 mx-0 right-column-container"> <div class="col mb-4"> <div class="card text-center bg-transparent border-0" style=height:33px><script async src="https://cse.google.com/cse.js?cx=c67a1214306f44e87" nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39></script><div class=gcse-searchbox-only></div> </div> </div> <div class="col mb-4"> <div class="card shadow"> <img src=/media/mvp.png class="bd-placeholder-img card-img-top" alt="Microsoft MVP - George Kosmidis"> <a href=https://mvp.microsoft.com/en-us/PublicProfile/5004591 target=_blank title="Microsoft MVP - George Kosmidis" class=stretched-link rel=noopener></a> </div> </div> <div class="col mb-4"> <div class="card shadow"> <img src=/media/azure-architecture-icons.png class="bd-placeholder-img card-img-top" alt="Azure Architecture Icons - SVGs, PNGs and draw.io libraries"> <a href=/azure-architecture-icons.html target=_top title="Azure Architecture Icons - SVGs, PNGs and draw.io libraries" class=stretched-link rel=noopener></a> </div> </div> </div> </div> </div> <div class=col-lg-4> </div> </div> </div> </div> </main> <footer class="footer mt-auto py-3 border-top"> <div class="container mx-auto text-center"> <a href=https://github.com/georgekosmidis target=_blank class=btn rel=noopener> <svg role=img class=icon viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>GitHub</title><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg> </a> <a href="https://www.linkedin.com/in/georgekosmidis/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>LinkedIn</title><path d="M20.447 20.452h-3.554v-5.569c0-1.328-.027-3.037-1.852-3.037-1.853 0-2.136 1.445-2.136 2.939v5.667H9.351V9h3.414v1.561h.046c.477-.9 1.637-1.85 3.37-1.85 3.601 0 4.267 2.37 4.267 5.455v6.286zM5.337 7.433c-1.144 0-2.063-.926-2.063-2.065 0-1.138.92-2.063 2.063-2.063 1.14 0 2.064.925 2.064 2.063 0 1.139-.925 2.065-2.064 2.065zm1.782 13.019H3.555V9h3.564v11.452zM22.225 0H1.771C.792 0 0 .774 0 1.729v20.542C0 23.227.792 24 1.771 24h20.451C23.2 24 24 23.227 24 22.271V1.729C24 .774 23.2 0 22.222 0h.003z"></path></svg> </a> <a href=https://www.youtube.com/c/GeorgeKosmidis target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>YouTube</title><path d="M23.498 6.186a3.016 3.016 0 0 0-2.122-2.136C19.505 3.545 12 3.545 12 3.545s-7.505 0-9.377.505A3.017 3.017 0 0 0 .502 6.186C0 8.07 0 12 0 12s0 3.93.502 5.814a3.016 3.016 0 0 0 2.122 2.136c1.871.505 9.376.505 9.376.505s7.505 0 9.377-.505a3.015 3.015 0 0 0 2.122-2.136C24 15.93 24 12 24 12s0-3.93-.502-5.814zM9.545 15.568V8.432L15.818 12l-6.273 3.568z"></path></svg> </a> <a href="https://sessionize.com/georgekosmidis/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 288 288" xmlns=http://www.w3.org/2000/svg><title>Sessionize</title><g transform="translate(0.000000,288.000000) scale(0.100000,-0.100000)"><path d="M135 2861 c-22 -10 -54 -34 -72 -52 -67 -71 -63 22 -63 -1369 0 -1391 -4 -1298 63 -1369 70 -73 42 -71 715 -71 l602 0 0 40 c0 108 -52 232 -127 301 -92 85 -229 125 -469 139 -119 6 -160 13 -190 28 -63 32 -96 81 -108 161 -6 43 12 87 470 1118 263 590 480 1073 483 1073 3 0 22 -42 41 -92 47 -127 157 -344 228 -453 156 -239 367 -450 607 -608 100 -65 373 -202 478 -238 37 -13 67 -27 67 -30 0 -3 -348 -160 -772 -349 -425 -188 -777 -346 -782 -350 -4 -5 11 -18 35 -30 235 -119 367 -331 394 -633 l7 -77 482 0 c444 0 484 2 521 19 51 23 111 89 125 138 8 27 10 249 8 773 -4 660 -6 743 -22 809 -69 289 -197 522 -396 721 -199 199 -432 327 -721 396 -67 16 -148 18 -819 21 -712 3 -747 2 -785 -16z m2069 -483 c99 -51 198 -181 200 -264 1 -57 -21 -79 -77 -78 -137 3 -328 220 -288 328 19 50 84 55 165 14z"></path></g></svg> </a> <a href=https://www.facebook.com/groups/msdevtech target=_blank class=btn rel=noopener> <svg xmlns=http://www.w3.org/2000/svg viewBox="0 0 400 400" role=img><path d="M353.701,0H55.087C24.665,0,0.002,24.662,0.002,55.085v298.616c0,30.423,24.662,55.085,55.085,55.085 h147.275l0.251-146.078h-37.951c-4.932,0-8.935-3.988-8.954-8.92l-0.182-47.087c-0.019-4.959,3.996-8.989,8.955-8.989h37.882 v-45.498c0-52.8,32.247-81.55,79.348-81.55h38.65c4.945,0,8.955,4.009,8.955,8.955v39.704c0,4.944-4.007,8.952-8.95,8.955 l-23.719,0.011c-25.615,0-30.575,12.172-30.575,30.035v39.389h56.285c5.363,0,9.524,4.683,8.892,10.009l-5.581,47.087 c-0.534,4.506-4.355,7.901-8.892,7.901h-50.453l-0.251,146.078h87.631c30.422,0,55.084-24.662,55.084-55.084V55.085 C408.786,24.662,384.124,0,353.701,0z" /></svg> </a> <a href="https://www.meetup.com/munich-dotnet-meetup/" target=_blank class=btn rel=noopener> <svg role=img viewBox="0 0 24 24" xmlns=http://www.w3.org/2000/svg><title>Meetup</title><path d="M6.98.555a.518.518 0 0 0-.105.011.53.53 0 1 0 .222 1.04.533.533 0 0 0 .409-.633.531.531 0 0 0-.526-.418zm6.455.638a.984.984 0 0 0-.514.143.99.99 0 1 0 1.02 1.699.99.99 0 0 0 .34-1.36.992.992 0 0 0-.846-.482zm-3.03 2.236a5.029 5.029 0 0 0-4.668 3.248 3.33 3.33 0 0 0-1.46.551 3.374 3.374 0 0 0-.94 4.562 3.634 3.634 0 0 0-.605 4.649 3.603 3.603 0 0 0 2.465 1.597c.018.732.238 1.466.686 2.114a3.9 3.9 0 0 0 5.423.992c.068-.047.12-.106.184-.157.987.881 2.47 1.026 3.607.24a2.91 2.91 0 0 0 1.162-1.69 4.238 4.238 0 0 0 2.584-.739 4.274 4.274 0 0 0 1.19-5.789 2.466 2.466 0 0 0 .433-3.308 2.448 2.448 0 0 0-1.316-.934 4.436 4.436 0 0 0-.776-2.873 4.467 4.467 0 0 0-5.195-1.656 5.106 5.106 0 0 0-2.773-.807zm-5.603.817a.759.759 0 0 0-.423.135.758.758 0 1 0 .863 1.248.757.757 0 0 0 .193-1.055.758.758 0 0 0-.633-.328zm15.994 2.37a.842.842 0 0 0-.47.151.845.845 0 1 0 1.175.215.845.845 0 0 0-.705-.365zm-8.15 1.028c.063 0 .124.005.182.014a.901.901 0 0 1 .45.187c.169.134.273.241.432.393.24.227.414.089.534.02.208-.122.369-.219.984-.208.633.011 1.363.237 1.514 1.317.168 1.199-1.966 4.289-1.817 5.722.106 1.01 1.815.299 1.96 1.22.186 1.198-2.136.753-2.667.493-.832-.408-1.337-1.34-1.12-2.26.16-.688 1.7-3.498 1.757-3.93.059-.44-.177-.476-.324-.484-.19-.01-.34.081-.526.362-.169.255-2.082 4.085-2.248 4.398-.296.56-.67.694-1.044.674-.548-.029-.798-.32-.72-.848.047-.31 1.26-3.049 1.323-3.476.039-.265-.013-.546-.275-.68-.263-.135-.572.07-.664.227-.128.215-1.848 4.706-2.032 5.038-.316.576-.65.76-1.152.784-1.186.056-2.065-.92-1.678-2.116.173-.532 1.316-4.571 1.895-5.599.389-.69 1.468-1.216 2.217-.892.387.167.925.437 1.084.507.366.163.759-.277.913-.412.155-.134.302-.276.49-.357.142-.06.343-.095.532-.094zm10.88 2.057a.468.468 0 0 0-.093.011.467.467 0 0 0-.36.555.47.47 0 0 0 .557.36.47.47 0 0 0 .36-.557.47.47 0 0 0-.464-.37zm-22.518.81a.997.997 0 0 0-.832.434 1 1 0 1 0 1.39-.258 1 1 0 0 0-.558-.176zm21.294 2.094a.635.635 0 0 0-.127.013.627.627 0 0 0-.48.746.628.628 0 0 0 .746.483.628.628 0 0 0 .482-.746.63.63 0 0 0-.621-.496zm-18.24 6.097a.453.453 0 0 0-.092.012.464.464 0 1 0 .195.908.464.464 0 0 0 .356-.553.465.465 0 0 0-.459-.367zm13.675 1.55a1.044 1.044 0 0 0-.583.187 1.047 1.047 0 1 0 1.456.265 1.044 1.044 0 0 0-.873-.451zM11.4 22.154a.643.643 0 0 0-.36.115.646.646 0 0 0-.164.899.646.646 0 0 0 .899.164.646.646 0 0 0 .164-.898.646.646 0 0 0-.54-.28z"></path></svg> </a> <div class=text-muted>© Copyright 2022, <a href="https://georgekosmidis.net/" target=_blank rel=noopener>George Kosmidis</a> <br> Is lawyer the most boring job in the world? Read the <a href=/privacy.html>Privacy Policy</a> to find out. <br><small class=text-black-50>Last Build: 2025-02-08T03:02:39+00:00 </small> </div> </div> </footer><script src=https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js integrity=sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p crossorigin=anonymous nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39></script><script src=https://cdn.jsdelivr.net/npm/cookieconsent@3.1.1/build/cookieconsent.min.js integrity="sha256-5VhCqFam2Cn+yjw61zbBNrbHVJ6SRydPeKopYlngbiQ=" crossorigin=anonymous nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39></script><script src=https://cdn.jsdelivr.net/npm/@highlightjs/cdn-assets@11.4.0/highlight.min.js integrity="sha256-GCgWKkl4RE3+M/TNH5d/F80Tz30PQT+Oubq5Q3I5c20=" crossorigin=anonymous nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39></script><script nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39>hljs.highlightAll();</script><script nonce=2f6efdd7-9a9b-4d86-b956-69985ba92a39>window.cookieconsent.initialise({"palette":{"popup":{"background":"#343c66","text":"#cfcfe8"},"button":{"background":"#f71559"}},"showLink":false,"theme":"classic","content":{"message":"Cookie Warning! My blog uses Google Analytics so Google knows you are here. Maybe others too, including FBI, CIA, NASA, ESA, IIS and other acronyms. If you don't like it, you must unfortunately go :(","dismiss":"It's just code, I 'll stay!"}});</script></body></html>