view for many to many

    1 /****** Object:  View [dbo].[vwTalent]    Script Date: 02/18/2009 15:11:11 ******/

    2 IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTalent]'))

    3 DROP VIEW [dbo].[vwTalent]

    4 GO

    5 /****** Object:  View [dbo].[vwTalent]    Script Date: 02/18/2009 15:11:15 ******/

    6 SET ANSI_NULLS ON

    7 GO

    8 SET QUOTED_IDENTIFIER ON

    9 GO

   10   

   11  

   12

   13 CREATE VIEW [dbo].[vwTalent]

   14 AS

   15 SELECT t.TalentID

   16 ,  t.FirstName 

   17 , (SELECT   l.LanguageName +  ', ' AS [text()]

   18         FROM [TalentLanguage] tl

   19         JOIN [Language] l ON tl.LanguageID = l.LanguageID 

   20         WHERE tl.TalentID = t.TalentID 

   21         ORDER by l.LanguageID

   22         FOR XML PATH('') 

   23    ) as LanguagesString

   24 , (SELECT   Convert(nvarchar(10), l.LanguageID) +  ', ' AS [text()]

   25         FROM [TalentLanguage] tl

   26         JOIN [Language] l ON tl.LanguageID = l.LanguageID 

   27         WHERE tl.TalentID = t.TalentID 

   28         FOR XML PATH('') 

   29    ) as LanguageIdsString  

   30 FROM Talent as t  

   31 WHERE t.Active = '1'

   32

   33 GO

select * from dbo.vwTalent

ID	FirstName	LanguagesString	LanguageIdsString
-----	-----------	--------------------	-----------
1	Jake	English,	1,
6	test	Chinese, Japanese, Romanian,	8, 9, 10,
7	Daren	English,	1,
32	Jim	NULL	NULL
33	Andy	English, Spanish, French, German, Polish, Portuguese,	1, 2, 3, 4, 5, 6,
34	Jeff	English,	1,

now you can call

  204 private IQueryable<Talent> AddSearch(IQueryable<Talent> t, string s)
  205 {
  206     return t.Where(tal =>  tal.LanguagesString.ToLower().Contains(s)  );
  207 }

Outer join Linq to Sql with a many-to-many

   79 public IQueryable<Model.Model.Talent> GetTalents()

   80 {

   81     var tal = from t in _db.Talents 

   82               join tre in _db.Responses on t.EyeColorID equals tre.ResponseID

   83               into tempEyes

   84               from rEyes in tempEyes.DefaultIfEmpty()  

   85               let tLanguage = GetTalentLanguages(t.TalentID)

   86               where t.Active == true

   87               select new Model.Model.Talent

   88               {

   89                   Id = t.TalentID,

   90                   FirstName = t.FirstName,

   91                   LastName = t.LastName,

   92                   EyeColorID = t.EyeColorID ?? -1,

   93                   EyeColor = rEyes.ResponseName, 

   94                   TalentLanguages = new LazyList<Model.Model.TalentLanguage>(tLanguage),

   95                   //LanguagesString = t.TalentLanguages.ToLanguageNameString(_LanguageRepository.GetLanguages()),

   96                   LanguagesString = String.Join(", "

   97                       ,(from tl in _db.TalentLanguages

   98                         join l in _db.Languages on tl.LanguageID equals l.LanguageID

   99                         where tl.TalentID == t.TalentID

  100                         select l.LanguageName.ToString()).ToArray())

  101               };

  102     return tal ;

  103 }

From D.R.Y. code

Intersect in Linq using Comparer

with help from Lost In LoC

  172         private static readonly char[] SplitDelimiters = " ".ToCharArray();
 ...

  215         private IQueryable<Talent> BasicSearch(string searchExpression)

  216         {

  217             IQueryable<Talent> t;

  218             string[] sa = searchExpression.Trim().Trim()
                                  .ToLower()
                                  .Split(SplitDelimiters,
                                         StringSplitOptions.RemoveEmptyEntries);

  219             t = _repository.GetTalents();

  220             foreach (string s in sa)

  221             {

  222                 t = t.Intersect(AddBasicSearch(s), new TalentComparer()); 

  223                 //http://lostinloc.com/2008/02/06/the-principle-of-least-astonishment/

  224             }

  225             return t;

  226         }

   13     public class TalentComparer : IEqualityComparer<Talent> // defines Equals and GetHashCode

   14     {

   15         public bool Equals(Model.Model.Talent x, Model.Model.Talent y)

   16         {

   17             return x.Id == y.Id;

   18         }

   19 

   20         // implements the IEqualityComparer.GetHashCode(T obj) : int

   21         public int GetHashCode(Model.Model.Talent obj)

   22         {

   23             return obj.Id.GetHashCode();

   24         }

   25     }

Linq to Lambda

I changed a Linq expression to a Lambda expression so I could refactor the filter. Here is the before and after

Before: (Linq expression)

results = from u in ctx.ActiveUsers

          where (u.CompanyID != 1 &&

                   (u.LastName.ToLower().Contains(searchString)

                   || u.Email.ToLower().Contains(searchString)

                   || u.Company.Name.ToLower().Contains(searchString)))

          orderby u.LastName, u.FirstName

          select new Employee

          {

              ID = u.ID,

              FirstName = u.FirstName,

              LastName = u.LastName,

              Email = u.Email,

              CompanyName = u.Company.Name,

              CompanyID = u.CompanyID.ToString()

          };

After: (Lambda expression)

results = ctx.ActiveUsers

    .Where(Employee.GetExpression(searchString))

    .OrderBy(u =>  u.LastName ).ThenBy(u => u.FirstName)

    .Select(u => new Employee {
      ID = u.ID

      , FirstName = u.FirstName
      , LastName = u.LastName

      , Email = u.Email
      , CompanyName = u.Company.Name

      , CompanyID = u.CompanyID.ToString() });

plus this to keep the where expression the same on the count:

private static Expression<Func<User, bool>> GetExpression(string searchString)

{ 

    Expression<Func<User, bool>> p = (u => u.CompanyID != 1 &&

                       (u.LastName.ToLower().Contains(searchString)

                       || u.Email.ToLower().Contains(searchString)

                       || u.Company.Name.ToLower().Contains(searchString)));

    return p;

}

so that GetExpression can be used here to make sure that our count query is the same as the select

public static int GetCustomerCount()

{

    UserContext ctx = new UserContext();

    int totalRecords;

    string searchString = SearchString; 

    totalRecords = ctx.ActiveUsers.Count(Employee.GetExpression(searchString));

    return totalRecords;

}

Linqpad

Linqpad is a nice way to learn how to write linq statements. There is no install required. Check it out here. Thanks Joseph

File uploading - howto

File uploading to a database was easy to implement with the help of this post

http://www.aspcode.net/File-uploading-howto-part-3.aspx

thanks aspcode.net. I am using linq to sql, so I had to modify a few things. Here is my code.

private IEnumerable<Report> cReport

{

    get { return (IEnumerable<Report>)this.Session["cReport"]; }

    set { this.Session["cReport"] = (IEnumerable<Report>)value; }

}

private ReportContext ctxReport

{

    get { return (ReportContext)this.Session["ctxReport"]; }

    set { this.Session["ctxReport"] = (ReportContext)value; }

}

protected void InsertButton_Click(object sender, EventArgs e)

{

    this.Page.Validate();

    if (this.Page.IsValid)

    {

        FileUpload fu = ((FileUpload)this.fvReport.FindControl("fuFile"));

        byte[] bData = new byte[fu.PostedFile.ContentLength];

        fu.PostedFile.InputStream.Read(bData, 0, fu.PostedFile.ContentLength);

        //Retrieve filename

        System.IO.FileInfo oInfo = new System.IO.FileInfo(fu.PostedFile.FileName); 

        xxx.TourAdmin.Data.Report rep = new xxx.TourAdmin.Data.Report();

        rep.active = true;

        rep.FileLength = fu.PostedFile.ContentLength;

        rep.FileName = oInfo.Name;

        rep.ReportData = bData; 

        this.ctxReport.Report.InsertOnSubmit(rep);

        this.ctxReport.SaveAll(); 

    }

    this.gvReports.DataBind();

}