Friday, January 20, 2012

Linq NOT IN clause

To emulate a SQL "NOT IN" clause like the following:

SELECT * FROM Clients WHERE ClientID NOT IN (select ClientID from AssignedClients)

We can do the following in Linq:

IEnumerable< Client > assignedClients = _repo.GetAssignedClients();
IEnumerable< Client > allClients = _repo.GetAllClients();
IEnumerable< Client > availableClients = from c in allClients
where !(assignedClients.Any(a => a.ClientID == c.ClientID))
select c;

Where Client has the following definition:

public class Client {
public int ClientID { get; set; }
public string ClientName { get; set; }
public bool IsActive { get; set; }
}


Another example:
var clientids = new int[] {1, 2, 3}; // IEnumerable of integers
var records = new Record[] { new Record{ClientId = 1}, new Record{ClientId = 9}, new Record{ClientId = 8}, new Record{ClientId = 2} };

var theRest = from r in records
              where !(clientids.Any(id => id == r.ClientId))
              select r;

And this is another way of obtaining the same
var theRest = records.Where(r => !clientids.Contains(r.ClientId));

They both will return the records whose ClientId is not in the list of clientids.

Wednesday, January 4, 2012

MapPath ASP.NET

MapPath sometimes refers to the directory the .NET solution is in and not the website and sometimes it does refers to the website itself. If we add the tilde character (~) it will return the website's path.

For example I have my solution at C:\Projects\src\MeinApp and inside that directory is the website (C:\Projects\src\MeinApp\WebService).
MapPath("/Schemas") will return 'C:\Projects\src\MeinApp\Schemas\' but
MapPath("~/Schemas") will return 'C:\Projects\src\MeinApp\WebService\Schemas'
which is what we finally want.

string dir = HostingEnvironment.MapPath("~/Schemas");

Kill All Active Connections To A Database

use master

ALTER DATABASE [Northwind] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Northwind] SET MULTI_USER

Thanks to:
http://goo.gl/QqmX2
http://goo.gl/xNM2m