Include variable as field name in linq statement

Question

Hi I have a link statement against a database table I did not create... The data structure is

Tbl_BankHols
BHDate .... Datetime
E ......... Bit
S ......... Bit
W ......... Bit
I ......... Bit

Basically it has a list of dates and then a value of 0 or 1 in E, S, W, I which indicate if that date is a bank holiday in England, Scotland, Wales and/or Ireland.

If I want to find out if a date is a bank holiday in any of the countries my Linq statement is

Dim BHQ = From d in db.Tbl_BankHols _
          Where d.BHDate = chkDate _
          Select d.BHDate

Where chkDate is the date I am checking. If a result is returned then the date is a bank holiday in one of the countries.

I now need to find out if chkDate is a bank holiday in a particular country how do I introduce that into the where statement?

I'm asking if this is possible before I think about changing the structure of the database. I was thinking of just having a single country field as a string which will contain values like E, EW, EWS, EWSI, I and other similar combinations and then I just use WHERE BCountry LIKE %X% (where X is the country I'm interested in). Or is there a better way?

As a side note, you don't need the `_` at line end. Not only in LINQ, but in whole VB — Teejay, May 28 '13 at 07:57
Jon... I should have explained.. The country will be a variable... There is a user table which has a field UCountry equal to E, I, W, S dependant on where they are located. I was wondering in I could pass this into the LINQ somehow. I suppose I have another option... Do a select on UCountry and dependant on Case create an appropriate LINQ as you have shown... — Mych, May 28 '13 at 08:05
If you are going to change the structure do it right, Have one table for countiries, then you can have a table of dates with foriegn keys to country. — Jodrell, May 28 '13 at 08:08

score 1 · Answer 1 · answered May 28 '13 at 08:05

1

Instead of changing the structure of your table the way you wrote, you could introduce a new Region (Code, Description) table and add a foreign key to your table pointing to the regions table. Your bank holidays table will then contain one record per (date/region) combination.

And your linq statement:

Dim BHQ = From d in db.Tbl_BankHols _
      Where d.BHDate = chkDate And d.Region = "England" _
      Select d.BHDate

answered May 28 '13 at 08:05

vc 74

37,131
7
73
89

Make it better (simpler) not worse. – Jodrell May 28 '13 at 08:09
That would work but I this as you say will create for example 4 records for Christmas day one for each region and on top of that I have the extra table. If I just have a single field with EWSI against Christmas day then the LIKE statement will pick this up without the need for 3 extra records and another table... Thanks for the suggestion though. – Mych May 28 '13 at 08:13
Ok as all of you are saying my structure change is not a good idea I will heed your advice.... Going down the two table with fk structure... Many thanks to all of you. You have all answere my question. – Mych May 28 '13 at 08:26

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Erm stop,

Your suggestion for extra denormalisation and using LIKE is a really "wrong" idea.

You need one table for countries, lets call it Country and another table for holidays, lets call it Holiday. The Country table should contain a row for each country in your system/model. The Holiday table should have two columns. One for the Date and a foriegn key to country, lets call it CountryId.

Then your linq could look something like,

db.Holiday.Any(Function(h) h.Country.Name = 
                    "SomeCountry" AndAlso h.Date = someDate)

The reasons why you shoudn't use LIKE for this are manifold but two major objections are.

LIKE doesn't perform well, its hard for an index to support it, and,

Lets imagine a situation where you need to store holidays for these countries,

Ecuador

El Salvador

Estonia

Ethiopia

England

Now, you have already assigned the code "E" to England, what code will you give to the others? No problem you say, "EL", "ET" ... but, already your LIKE "%E" condition is broken.

Here are the scripts for the schema I would go with.

CREATE TABLE [Country](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ));

CREATE TABLE [Holiday](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [CountryId] [int] NOT NULL FOREIGN KEY Country(Id),
    CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ));

Jodrell, I thank you for your advice and I will be going down this route. At the moment this app is destined for a UK client only with no requirements for international considerations given in the remit. But as we all know it's best to think ahead in this game. — Mych, May 28 '13 at 11:59
@Mych In that case I'd suggest the `Name` column is larger than necessary. This more normalised approach is even better if you had a `Country` enumeration where the values matched up with `Country.Id`, then you would'nt want an identity column, since the values would be fixed. To anybody who is interested, here are the UK public holidays for the next few years https://www.gov.uk/bank-holidays — Jodrell, May 28 '13 at 13:21

score 0 · Answer 3 · edited May 23 '17 at 12:18

0

before I think about changing the structure of the database

You can do it by composing a query with OR predicates by using LINQKit.

Dim IQueryable<BankHoliday> BHQ = ... (your query)

Dim pred = Predicate.False(Of BankHolifday)

If countryString.Contains("E")
    pred = pred.Or(Function(h) h.E)
EndIf
If countryString.Contains("S")
    pred = pred.Or(Function(h) h.S)
EndIf
...

Return query.Where(pred.Expand())

(I'm not fluent in VB so there may be some error in there)

See this answer for a similar example.

edited May 23 '17 at 12:18

Community

1
1

answered May 28 '13 at 09:16

Gert Arnold

105,341
31
202
291

Gert thanks for this. I have decided to go with the structure change as it normalises and makes the system scalable. The table currently holds some 20 odd records ( the number of different bank holidays in the UK over the next 3 years) so its not a big job. – Mych May 28 '13 at 12:04

score 0 · Answer 4 · answered May 28 '13 at 09:47

While the DB structure is not optimal, if you are working with legacy code and there's no effort given for a full refactor, you're gonna have to make do (I feel for you).

The simplest option I think you have is to select not just the date, but also the attributes.

Dim BHQ = From d in db.Tbl_BankHols _
          Where d.BHDate = chkDate _
          Select d

This code will give you d.S, d.E, d.W, and d.I, which you can use programmatically to determine if the holiday applies to whatever country you are currently working on. This means outside the query, you will have a separate if statement which would qualify if the holiday applies to the country you are processing.

Include variable as field name in linq statement

4 Answers4