SCOPE_IDENTITY() with LINQ

While reading about a recent question concerning @@Identity and the reasons to use SCOPE_IDENTITY() instead I experimented with LINQ to get the SCOPE_IDENTITY() results.

 using (var dbc = new siteDataContext())
    {
        dbc.Log = Console.Out;
        repertoire newSkill = new repertoire
            {
                skill = "a new item"
            };
        dbc.repertoires.InsertOnSubmit(newSkill);
        dbc.SubmitChanges();
        Console.Write(newSkill.id.ToString());
    }

It seems that calling the id column of the inserted object gives you the SCOPE_IDENTITY(). Here is the SQL Log:

INSERT INTO [dbo].[repertoire]([skill])
VALUES (@p0)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input NVarChar (Size = 10; Prec = 0; Scale = 0) [a new item]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

Working on Bible Verse Tags for ASP.NET (C#)

To do:

  • build Regex to detect bible verses in tags
  • put in named back-references
  • find a way to choose different tables with LINQ
  • make it work for an entire chapter
  • add error messages
  • move it to a class file instead of in the page
  • run it from the filterText method if IndexOf(“[bible]”) !=-1
  • fix toVerse in case it goes out of range
  • fix misspelled or abbreviated bookName with value from database
  • change to Compiled Queries
  • add footnotes
  • pretty up error messages
  • clean up final code
public class bibleParts
{
	public static Func<bibleDataContext, string, IQueryable<bibleParts>>
		theVersion = CompiledQuery.Compile((bibleDataContext context, string bibleVersion) =>
			(from v in context.bibles_infos
			 where v.bibleName == bibleVersion
			 select new bibleParts
				 {
					 version = v.bibleID,
					 language = v.language,
					 copyright = v.copyright
				 }));

	public static Func<bibleDataContext, int, string, IQueryable<bibleParts>>
		theBook = CompiledQuery.Compile((bibleDataContext context, int bookLang, string book) =>
			(from b in context.book_names
			 where (b.book == book || b.abbreviation == book || b.alt == book) && b.language == bookLang
			 select new bibleParts
			 {
				 bookID = b.book_id,
				 book = b.book
			 }));
	public static Func<bibleDataContext, int, int, int, IQueryable<bibleParts>>
		theVerses = CompiledQuery.Compile((bibleDataContext context, int version, int book, int chapter) =>
			(from v in context.verses
			 where v.versionID == version && v.bookID == book && v.chapter == chapter
			 select new bibleParts
			 {
				 verseNum = v.verse1,
				 thisVerse = v.text
			 }));

	public int version {get;set;}
	public int language {get;set;}
	public string copyright {get;set;}
	public int bookID {get;set;}
	public string book {get;set;}
	public int verseNum {get;set;}
	public string thisVerse {get;set;}
}
</pre></pre>
<pre><pre>private string verseMod(string text)
{
	string textVal = text;
	string errorValue = "";
	string verseNumBegin = "<sup>";
	string verseNumEnd = "</sup>";
	string footerBegin = "<em> - ";
	string footerEnd = "</em>";
	string bibleVersion = "kjv";  //default version if none is specified
	string pat = @"\[bible[=]?(?<version>[a-zäëïöüæø]*)](?<entire>(?<book>([0-9][\s]?)?[a-zäëïöüæø]*[\s]{1}([a-zäëïöüæø]*[\s]?[a-zäëïöüæø]*[\s]{1})?)(?<chapter>[0-9]{1,3})(:{1}(?<verse>[0-9]{1,3})(-{1}(?<toVerse>[0-9]{1,3}))?)?)\[/bible]";

	Regex r = new Regex(pat, RegexOptions.IgnoreCase);

	MatchCollection m = r.Matches(textVal);
	foreach (Match match in m)
	{
		string verseVal = "";
		string errorText = "";
		if (!String.IsNullOrEmpty(match.Groups["version"].Value))
			bibleVersion = match.Groups["version"].Value;
		string thisBook = match.Groups["book"].Value;
		int fromVerse = 0;
		int toVerse;
		if (!String.IsNullOrEmpty(match.Groups["verse"].Value))
			fromVerse = Convert.ToInt32(match.Groups["verse"].Value);
		toVerse = fromVerse;
		if (!String.IsNullOrEmpty(match.Groups["toVerse"].Value))
			toVerse = Convert.ToInt32(match.Groups["toVerse"].Value);

		using (var bdc = new bibleDataContext())
		{
			var searchVersion = bibleParts.theVersion(bdc, bibleVersion).SingleOrDefault();
			if (searchVersion != null)
			{
				var searchBook = bibleParts.theBook(bdc, searchVersion.language, thisBook).SingleOrDefault();
				if (searchBook != null)
				{
					var searchVerses = bibleParts.theVerses(bdc, searchVersion.version, searchBook.bookID, Convert.ToInt32(match.Groups["chapter"].Value));
						if (fromVerse != 0)
						{
							searchVerses = from v in searchVerses
										   where v.verseNum >= fromVerse && v.verseNum <= toVerse
										   select v;
						}
							string realToVerse = "";
							verseVal += String.Format("{1}{0}{2}", match.Groups["entire"].Value.Replace(match.Groups["book"].Value.Trim(), searchBook.book), "<span class=\"bibleHead\">", "</span>");
							verseVal += "<span class=\"bibleContent\">";
							foreach (var theVerse in searchVerses)
							{
								string verseFormat = "{2}{1}{3}{0} ";
								if (String.IsNullOrEmpty(match.Groups["toVerse"].Value) && !String.IsNullOrEmpty(match.Groups["verse"].Value))
									verseFormat = "{0} ";

								verseVal += String.Format(verseFormat, theVerse.thisVerse, theVerse.verseNum, verseNumBegin, verseNumEnd);
								realToVerse = theVerse.verseNum.ToString();
							}
							verseVal += String.Format("{1}{0}{2}</span>", searchVersion.copyright, footerBegin, footerEnd);
							verseVal = verseVal.Replace(String.Format("-{0}", toVerse), String.Format("-{0}", realToVerse));
							verseVal = verseVal.Replace("-</span>", "</span>");
				}
				else
				{
					errorText = "The book \"{0}\" does not exist.  Please check the spelling and try again.";
					errorValue = match.Groups["book"].Value;
				}
			}
			else
			{
				errorText = "The version \"{0}\" is not installed on this server";
				errorValue = match.Groups["version"].Value;
			}
			if (String.IsNullOrEmpty(errorText))
			{
				textVal = textVal.Replace(match.Groups[0].Value, verseVal);
			}
			else
			{
				textVal = textVal.Replace(match.Groups[0].Value,String.Format(errorText,errorValue.Trim()));
			}
		}
	}
	return textVal;
}

Currently active at newcastlechurchofChrist.com.