NutrishSr28.EF.Core

🦖 Gregory Eakin

This is an experiment in configuring an existing USDA Nutrition Database in Entity Framework Core.

Steps to setup SQL Local DB:

1. Unzip the Full Version of the SR28 ASCII file format into the data folder.
2. Unzip the patch file (May 2016) into the data2 folder. Overwrite the DATASRCLN.txt and sr28_doc.pdf

cd ~/source/NutrishSr28.EF.Core
curl https://www.ars.usda.gov/ARSUserFiles/80400535/DATA/SR/sr28/dnload/sr28asc.zip --output sr28asc.zip
mkdir data/
unzip sr28asc.zip -d ar28asc/

SqllocalDB i
SqllocalDB create "SR28" -s
sqlcmd -S "(localdb)\SR28" -Q "CREATE DATABASE Nutrish"
cd DBSetup
dotnet ef database update
dotnet run
cd ../DBSetup.Tests
dotnet test

Convert over to PostgreSQL:

cd DBSetup
rm migrations/*
dotnet ef migrations remove
dotnet ef migrations add Postgresql
dotnet ef database update
dotnet run

Database:

US Department of Agriculture, Agricultural Research Service. 2016. Nutrient Data Laboratory. USDA National Nutrient Database for Standard Reference, Release 28 (Slightly revised). Version Current: May 2016. http://www.ars.usda.gov/nea/bhnrc/mafcl

Tools:

• Entity Framework Core
• SQL Server
• SQL Server LocalDB
• SQL Server Management Sdutio
• PostgreSQL
• pgAdmin
• CsvHelper
• Visual Studio
• ReSharper
• Unit Tests
• Git Extensions

Author

🔥 Greg Eakin

PostgreSQL DB Stuff

docker exec -it postgres-db-1 psql -U greg -c "CREATE database SR28;"
docker exec -it postgres-db-1 psql -U greg -c "CREATE USER docker;"
docker exec -it postgres-db-1 psql -U greg -c "ALTER USER docker WITH PASSWORD 'secret';"
docker exec -it postgres-db-1 psql -U greg -c "GRANT ALL PRIVILEGES ON DATABASE SR28 TO docker;"
docker exec -it postgres-db-1 psql -U greg -c "ALTER USER docker WITH SUPERUSER;"

Common Measure of Foods

SELECT * FROM "SR28"."FOOD_DES"
where 
	"SR28"."FOOD_DES"."NDB_No" = '03213'
ORDER BY "NDB_No" ASC 

SELECT * FROM "SR28"."WEIGHT"
where 
	"SR28"."WEIGHT"."NDB_No" = '03213'
ORDER BY "NDB_No" ASC, "Seq" ASC 

select 
	"SR28"."NUTR_DEF"."NutrDesc",
	"SR28"."NUTR_DEF"."Units",
	"SR28"."NUT_DATA"."Nutr_Val"
from 
	"SR28"."NUTR_DEF" 
inner join 
	"SR28"."NUT_DATA" 
on 
	"SR28"."NUTR_DEF"."Nutr_No" = "SR28"."NUT_DATA"."Nutr_No" 
where 
	"SR28"."NUT_DATA"."NDB_No" = '03213'
order by
	"SR28"."NUTR_DEF"."SR_Order"

The following formula is used to calculate the nutrient content per household measure:

N = (V*W)/100 

where:

N = nutrient value per household measure, 
V = nutrient value per 100 g (Nutr_Val in the Nutrient Data file), and 
W = g weight of portion (Gm_Wgt in the Weight file).