Linq - Convert VB.Net to C# help with on query

Question

We're slowly converting some code from VB.Net to C#, so I'm trying to learn the correct syntax in C#. Could someone help with this conversion? Basically I pull from a view all values that have not already been selected (saved) in another table and do a search on the items.

Employees a,b,c,d are in the Employee table. I have already selected and saved employee a and b. Now, when I re-run the search it should exclude employees a and b and only search for c and d if the search criteria is met.

This works fine in the vb.net query. I'm trying to figure out how to translate.

Thanks for any help! I think it's a very simple translation, I'm just having a bit of trouble with it.

Dim query = From tmp In context.vw_EmployeeDemographics _
     Where Not (From jitrv In context.JITRuleValidations 
     Join e In context.Employees On jitrv.Employee_RecordID Equals e.RecordID _
     Where jitrv.Parent_RecordID = Parent_RecordID _
  Select e.RecordID).Contains(tmp.Parent_RecordID) And 
  context.fn_ConcatName(tmp.FirstName, tmp.MiddleName,
      tmp.LastName).Contains(_master.pSearchValue1) _
      Order By tmp.LastName.ToUpper(), tmp.FirstName.ToUpper() _
  Select FirstName = tmp.FirstName, MiddleName = tmp.MiddleName, 
  LastName = tmp.LastName, RecordID = tmp.EmployeeID, _
DisplayText = context.fn_ConcatName(tmp.FirstName, tmp.MiddleName, tmp.LastName)

Jon Skeet · Accepted Answer · 2010-01-08 14:48:49Z

2

Okay, as a verbatim conversion:

var query = from tmp in context.vw_EmployeeDemographics
            where !(from jitrv in context.JITRuleValidations
                    join e in context.Employees
                    on jitrv.Employee_RecordID equals e.RecordID
                    where jitrv.Parent_RecordID == Parent_RecordID
                    select e.RecordID).Contains(tmp.Parent_RecordID)
                  && context.fn_ConcatName(tmp.FirstName, tmp.MiddleName, tmp.LastName)
                            .Contains(_master.pSearchValue1)
            orderby tmp.LastName.ToUpper(), tmp.FirstName.ToUpper()
            select new { tmp.FirstName, tmp.MiddleName, tmp.LastName,
                         RecordID = tmp.EmployeeID,
                         DisplayText = context.fn_ConcatName(tmp.FirstName, 
                                                     tmp.MiddleName, tmp.LastName) };

The bit I'm not sure about is the Select part in the VB - I'm guessing that it automatically creates a new anonymous type (as does the C# query) but I'm not sure.

I think that should do it - but I strongly recommend that you refactor it into something more readable :) Here are a few changes:

var validations = from jitrv in context.JITRuleValidations
                  join e in context.Employees
                  on jitrv.Employee_RecordID equals e.RecordID
                  where jitrv.Parent_RecordID == Parent_RecordID
                  select e.RecordID;

var query = from tmp in context.vw_EmployeeDemographics
            let DisplayText = fn_ConcatName(tmp.FirstName,
                                            tmp.MiddleName, 
                                            tmp.LastName) 
            where !validations.Contains(tmp.Parent_RecordID)
               && DisplayText.Contains(_master.pSearchValue1)
            orderby tmp.LastName.ToUpper(), tmp.FirstName.ToUpper()
            select new { tmp.FirstName, tmp.MiddleName, tmp.LastName,
                         RecordID = tmp.EmployeeID, DisplayText };

answered Jan 8, 2010 at 14:48

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GalacticCowboy Over a year ago

You can also split the two conditions into separate "where" clauses. i.e. where !validations.Contains(tmp.Parent_RecordID) where DisplayText.Contains(_master.pSearchValue1) I like to do this whenever the clauses are not related to one another - makes it cleaner and more readable, IMHO.

sugarcrum Over a year ago

Thanks for the response. This actually helps me alot with some other questions I had. Now, on to purchasing a book on C# LINQ :)

Collectives™ on Stack Overflow

Linq - Convert VB.Net to C# help with on query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related