Linq-2-Sql code: Does this scale?

Question

I'm just starting to use linq to sql. I'm hoping that someone can verify that linq-2-sql has deferred execution until the foreach loop is executed. Over all, can someone tell me if this code scales. It's a simple get method with a few search parameters. Thanks!

Code:

public static IList<Content> GetContent(int contentTypeID, int feedID, DateTime? date, string text)
        {
            List<Content> contentList = new List<Content>();
            using (DataContext db = new DataContext())
            {
                var contentTypes = db.ytv_ContentTypes.Where(p => contentTypeID == -1 || p.ContentTypeID == contentTypeID);
                var feeds = db.ytv_Feeds.Where(p => p.FeedID == -1 || p.FeedID == feedID);

                var targetFeeds = from f in feeds
                                  join c in contentTypes on f.ContentTypeID equals c.ContentTypeID
                                  select new { FeedID = f.FeedID, ContentType = f.ContentTypeID };

                var content = from t in targetFeeds
                              join c in db.ytv_Contents on t.FeedID equals c.FeedID
                              select new { Content = c, ContentTypeID = t.ContentType };

                if (String.IsNullOrEmpty(text))
                {
                    content = content.Where(p => p.Content.Name.Contains(text) || p.Content.Description.Contains(text));
                }

                if (date != null)
                {
                    DateTime dateTemp = Convert.ToDateTime(date);
                    content = content.Where(p => p.Content.StartDate <= dateTemp && p.Content.EndDate >= dateTemp);
                }

                //Execution has been defered to this point, correct?
                foreach (var c in content)
                {
                    Content item = new Content()
                    {
                        ContentID = c.Content.ContentID,
                        Name = c.Content.Name,
                        Description = c.Content.Description,
                        StartDate = c.Content.StartDate,
                        EndDate = c.Content.EndDate,
                        ContentTypeID = c.ContentTypeID,
                        FeedID = c.Content.FeedID,
                        PreviewHtml = c.Content.PreviewHTML,
                        SerializedCustomXMLProperties = c.Content.CustomProperties
                    };
                    contentList.Add(item);
                }
            }
            //TODO
            return contentList;
        }

Answer 1

Depends on what you mean with 'scales'. DB side this code has the potential of causing trouble if you are dealing with large tables; SQL Server's optimizer is really poor at handling the "or" operator in where clause predicates and tend to fall back to table scans if there are multiple of them. I'd go for a couple of .Union calls instead to avoid the possibility that SQL falls back to table scans just because of the ||'s.

If you can share more details about the underlying tables and the data in them, it will be easier to give a more detailed answer...