Why EntitySet not auto-generated by LINQ-to-SQL?

It turns out the the child table must have a primary key. If not set in database level, that can be set in the *dbml file. Multiple-field primary key works too.

Using Full Text Search with LINQ

LINQ does not natively support full text search keywords (FREETEXT, CONTAINS etc).  Actually the compiler won’t complain about them but just translate everything to LIKE.

Here is the work around:

1. Create a table-valued function that takes in keyword as the parameter:

CREATE function [dbo].[SearchJobAll]
(@keywords nvarchar(4000))
returns table
AS    return (SELECT * FROM CONTAINSTABLE( dbo.job,*,@keywords))

2. Join this function as a table:

from l in db.Jobs join s in db.SearchJobAll(keyword) on l.JobID equals s.KEY select l;

(Note: CONTAINSTABLE return only 2 columns: KEY and RANK)

LINQ: add association for database views

The LINQ to SQL Designer automatically create association for tables that have foreign keys defined. However for database views, it won’t because there is no foreign keys relationship in views. Manually adding association in the Designer will not create “private EntitySet” collection.

The key is to set Primary key in the Designer, and BOTH tables/views need to have a primary key, then adding association will create the appropriate child collection!

No database-like triggers in LINQ, and things to look out

In my LINQ application, I was looking for a UPDATE trigger implementation (in LINQ of course, not in DB), so that whenever a record is taken offline, I can record the time and user who does that. This actually can NOT be done in database trigger since I’m not passing over the logged in user info to DB.

It turned out that it’s impossible to do it in DataContext Level as I originally hoped.

First of all, the OnxxxChanged and OnxxxChanging events are mainly for validation and logging purpose. Modification to the value won’t be saved to DB.

Another option is to override partial UpdateXXXX in DataContext class.  However you will have to implement the complete UPDATE yourself. This is not what I want. I wanted something exactly like database trigger – something you do AFTER the DB transaction, without changing the default update behavior. Besides, LINQ doesn’t provide the old/new value in that partial function call.

What I had to do is at the UI level – handling ItemUpdating of my ListView control:

if (((bool)e.OldValues["Active"]) == true && ((bool)e.NewValues["Active"]) == false)
{
e.NewValues["RemoveDate"] = DateTime.Now;
e.NewValues["RemovedBy"] = Security.GetCurrentUserName();
}
else if (((bool)e.OldValues["Active"]) == false && ((bool)e.NewValues["Active"]) == true)
{
e.NewValues["RemoveDate"] = null;
e.NewValues["RemovedBy"] = String.Empty;
}

This is disappointing.

Another thing to note is that if there is DB side INSERT/UPDATE trigger that changes table value, then the DB value might be different from the LINQ in-memory value, and will cause update error next time LINQ updates. Of course there won’t be any problem if there is a reload before the update (which will be 99% of the case).

LINQ: cache with (child) collection properties

By default, because of the “Lazy Loading” setting, LINQ won’t load the child properties collection data until they are first accessed. So for the caching purpose, we have to force it to load everything before releasing the DataContext.  This is what you should do:

public static List<ParentObject> GetPremiumsByCompany(int companyID)
{
using (PolicyDataSourceDataContext db = new PolicyDataSourceDataContext())
{
db.ObjectTrackingEnabled = false; //optional. only if the data is readonly
// have to set options otherwise the collection will not be populated
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<ParentObject>(p => p.ChildObject);
db.LoadOptions = dlo;
List<ParentObject> ret = (from pre in db.ParentObjects
select pre).ToList();
return ret;
}
}

Caching with LINQ (“delayed” execution)

To increase the site performance, I cached (at least I thought I did) the result from a LINQ query into Application.Cache. The code is like this:

public static List<Region> GetAllRegions()
{
if (MyCache.GetCache(“RegionListFromDB”) == null)
{
using (MyInfoDataContext db = new MyInfoDataContext())
{
var regions = from r in db.Regions
select r;
MyCache.AddToAbsoluteCache(“RegionListFromDB”, regions, SiteConfig.LongCache);
return regions as List<Region>;
}
}
else
return MyCache.GetCache(“RegionListFromDB”) as List<Region>;
}

It looks good, when I debug through it I did see that the copy in the cache was returned to the caller. However the SQL Profiler told me that the database still got hit every time. It turned out that as soon as the caller accessed the returned List<>  in foreach loop, the database will be accessed. It looks like that it only cached the “command” to get the data, not the data itself.

Later I found out that by default LINQ uses “delayed” execution/loading, until the very first time the data is needed. Some methods will force immediate loading: Count(), Max()…. and ToList(), ToArray(). So this code change made my caching working:

List<Region> reList = (from r in db.Regions select r).ToList();
MyCache.AddToAbsoluteCache(“RegionListFromDB”, reList, SiteConfig.LongCache);

There is another evidence of this “delayed” execution. See this method to get total item count from a table:

var items = from i in db.ForSaleItems
where i.SiteID == SiteConfig.SiteID
select i;
items = from i in items
where  i.Region.RegionNameEN == region  && i.Category.CategoryNameEn == category
select i;
if (items2 != null)
return items.Count();
else
return 0;

Through SQL Profiler, I can see that the database was only accessed once, and that’s by command items.Count(), and the SQL query string is the combination of both LINQ query.  You can put if statements to build LINQ query conditionally.

Another trick, if the data is reayonly, set ObjectTrackingEnabled = false on the LINQ data context to speed up the loading.