Tag: SQL Server

View Raw SQL in EF Core – Simple Option

Hi everyone,

I’ve been looking for a simple way of viewing the raw output of sql in my local environment without having to make code changes and came across the following config setup:

{
“Logging”: {
“LogLevel”: {
“Default”: “Debug”,
“System”: “Information”,
“Microsoft”: “Information”
}
}

This will show the sql statements in your output window without requiring any additional code:
sql-output

Thanks to this stackoverflow post for the answer: https://stackoverflow.com/a/54704006/522859

Geography: one of the identified items was in an invalid format – Entity Framework

Hey everyone,

I’m currently working on a prototype using .net core, sql server, ef and NetTopologySuite to handle locations. While trying to save a location I ran into the following error:

Geography: one of the identified items was in an invalid format

My code was as follows:

using GeoAPI.Geometries;
using NetTopologySuite;
using NetTopologySuite.Geometries;

...
var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
...
Location = geometryFactory.CreatePoint(new Coordinate(request.Lat, request.Lng))
...

The solution ended up being pretty straight-forward. Simply swap the latitude and longitude values when creating a coordinate:

Location = geometryFactory.CreatePoint(new Coordinate(request.Lat, request.Lng))

// Use this instead
Location = geometryFactory.CreatePoint(new Coordinate(request.Lng, request.Lat))

This is mentioned pretty clearly when reading the documents but I somehow skipped over it. Hopefully this will be able to help out anyone else who does the same thing!

Official docs: https://docs.microsoft.com/sv-se/ef/core/modeling/spatial

Set Date Back Twelve Hours – MSSQL

Hey everyone,

A quick post on how to set a date value for twelve hours ago using SQL Server:

UPDATE MyTable
SET MyDate = dateadd(HOUR, -12, CURRENT_TIMESTAMP)

Simply use the dateadd function and specify the unit (hours in this case). The example above will set MyDate to twelve hours ago.

Here’s the offical documentation on the function: https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017

Also, thanks to this stackoverflow post for the info: https://stackoverflow.com/a/18518412/522859

Movies.mdf not in App_data Folder – MVC4 Tutorial

Hey everyone,

I’ve decided to take a look MVC4 (C#) and have just started the Movies tutorial. Unfortunately I ran into a bit of trouble with the database setup, for some reason mine wasn’t appearing in the app_data folder.

Apparently there are a few things you can try here:

Click Show All Files
This one is pretty simple, just click Show All Files
– Project > Show All Files

Refresh
Hit the refresh button
– Project > Refresh

Ensure that you’ve created a movie
You’ll need to make sure you’ve created a movie first. Apparently the database is not created until after a new row is inserted.

Final Solution, also the one that worked for me
The database wasn’t actually created under app_data, instead it was placed under: C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLDATA

This StackOverflow post explains why: asp-net-retrieving-data-from-nowhere