Recently I wanted to insert a row in a database from a flat file. But I needed to check if db already contained that data. So how to do that with Linq? With SQL it would have been simple. Just use the exists statement. But it´s not that straight forward with linq. Or...is it?

Check this out:

if(!db.tblPerson.Any(p=>p.DateOfBirth == DateOfBirth))
//Do the insert dance :) 

On a project recently, I had a database server and a web server with same date but different time. Since I had no rights to do something about that I was´nt able to use DateTime.Now in my web app and getdate() in the database.

I found 3 solutions to this:

1 - Create a view where getdate() is selected, and call it with Linq

2 - Call execute query like this:

DateTime myDate = DataContext.ExecuteQuery<DateTime>("select getdate()").Single;

3 - Create a new function in you´re data context file:

[Function(Name="getdate", IsComposable=true)]public DateTime GetDBServerDate(){  MethodInfo myInfo = MethodBase.GetCurrentMethod() as MethodInfo;  return (DateTime)this.ExecuteMethodCall(this, myInfo, null).ReturnValue;}

I added a very simple search function on one of my sites. It contain some textboxes and a (search)button. My code behind looks like this:

private void PerformSearch()
{
 DataClassesDataContext db = new DataClassesDataContext();

  var result = fromquery in db.tblPerson
   where
    (SqlMethods.Like(query.FirstName, "%" + firstName + "%") || firstName == "")
     && (SqlMethods.Like(query.LastName,"%" + lastName + "%") || lastName == "")
   orderby query.LastName, query.FirstName
   select new {query.FirstName, query.LastName, query.Address, query.Phone, query.Mail };

  rSearchResult.DataSource = result;
  rSearchResult.DataBind();
  db.Connection.Close();
}

rSearchResult is a repeater on my .aspx page to witch I bind the result.
The namespace for SqlMethods is System.Data.Linq.SqlClient.

So what is it that I do here? Well...I compare firstName and lastName (strings with search value) with data in my database. But I can also search only on first name or last name, because I also check if the string is blank. So if I leave my Firstname textbox (on my search.aspx page) blank and fill my Lastname textbox with "Andersson", this function will return everyone with Andersson as their last name since my string firstName has the value "".