Drunktender may not be the world’s most complex program but it has some thought put into it.  I made a mistake when I designed my database and it cause a bit of complexity for one stored procedure and I decided that when making a drink, brand shouldn’t matter, the type of alcohol is more important.  My system, currently, will never have 10 types of vodka so why should I specify that a Vodka and Sprite should have Grey Goose by default.  That should be made at a later level.  The drink contains Vodka, plain and simple.

So how do I correct a relationship based on ingredients to ingredient type instead?  First, go to the database and correct the relationship.  I renamed IngredientId in DrinkRecipe to IngredientTypeId and updated the foreign key relationship appropriately.  Then in Visual Studio, I corrected my Linq2Sql DBML file to reflect this change.

Updating the logic in the code behind was fairly easy due to the abstraction of business layers in my project.  The spots of code I did alter had to do with the drink object and the admin sections that added in drinks since I had to switch the context from ingredients to ingredient types.  One “gotcha!” bug I realized with Linq 2 SQL when I was correcting my data was deleting objects that were connected to the parent object then wanting to reuse them.  My solution was to just clone the list.

// deleting will destroy foreign keys so will need to clone list
var temp = new List<DrinkRecipe>();
drinkRecipes.ForEach(
	dr => temp.Add(new DrinkRecipe
	               	{
	               		Amount = dr.Amount,
	               		PourOrder = dr.PourOrder,
	               		IngredientTypeId = dr.IngredientTypeId
	               	})
	);
DrinkRecipes.DeleteByDrinkId(id);
drinkRecipes = temp;

The stored procedure to return all the drinks that the system could make was made far easier now since I didn’t have to figure out what types of alcohol the Drink contained and what types of alcohol the hookup table contained.  As you can see, it is extremely easy to figure out that data now.  I cut out a few lines of SQL.  It still uses a double negation but gets the job done.

SELECT * FROM drinks as d
WHERE d.Name like '%' + @PartialName + '%' and
d.drinkid NOT IN
(
	SELECT dr.drinkid FROM
	DrinkRecipes dr
	WHERE NOT EXISTS
	(
		select * from IngredientTypes it
		INNER JOIN Ingredients ii ON ii.IngredientTypeId = it.IngredientTypeId
		INNER JOIN hookups h ON ii.IngredientID = h.IngredientId
		AND dr.IngredientTypeId = ii.IngredientTypeId
	)
)
Order By d.Name