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
– Teejay, Commented May 28, 2013 at 7: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
– Mych, Commented May 28, 2013 at 8: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
– Jodrell, Commented May 28, 2013 at 8:08

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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
    ));

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 28, 2013 at 8:14

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

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

2 Comments

Mych Over a year ago

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.

Jodrell Over a year ago

@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 gov.uk/bank-holidays

vc 74 · Accepted Answer · 2013-05-28 08:05:00Z

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, 2013 at 8:05

vc 74

38.3k7 gold badges80 silver badges96 bronze badges

3 Comments

Jodrell Over a year ago

Make it better (simpler) not worse.

Mych Over a year ago

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 Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:18:52Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered May 28, 2013 at 9:16

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

1 Comment

Mych Over a year ago

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.

Jeric Cantos · Accepted Answer · 2013-05-28 09:47:34Z

0

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.

answered May 28, 2013 at 9:47

Jeric Cantos

2781 silver badge7 bronze badges

Collectives™ on Stack Overflow

Include variable as field name in linq statement

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related