none
LINQ Using Foreign Key RRS feed

  • Question

  • I have 2 tables : Projects and VejpSelection (Table definitions below)
    CREATE TABLE [dbo].[Projects] (
        [ProjID]        INT            IDENTITY (1, 1) NOT NULL,
        [Name]          VARCHAR (255)  NOT NULL,
        CONSTRAINT [PK_dbo.Projects] PRIMARY KEY CLUSTERED ([ProjID] ASC)
    );
    and 
    CREATE TABLE [dbo].[VejpSelection] (
        [VejpSelectionId] INT  IDENTITY (1, 1) NOT NULL,
        [Pid]             INT  NOT NULL,
        [Selection_Study] TEXT NULL,
        [Selection_Who]   TEXT NULL,
        [Selection_Start] TEXT NULL,
        CONSTRAINT [PK_dbo.VejpSelection] PRIMARY KEY CLUSTERED ([VejpSelectionId] ASC),
        CONSTRAINT [VEJPSEL_FK_PROJECTS] FOREIGN KEY ([Pid]) REFERENCES [dbo].[Projects] ([ProjID]) ON DELETE CASCADE ON UPDATE CASCADE

    );

    I want to be able to select the row in VejpSelection where VejpSelection.Pid = Projects.ProjId

    I figure I need to write a LINQ query like this:
    Dim vejpselectionQuery = From selection In db.VejpSelections
                                                 Where selection.Pid = id

                                                 Select selection

    But now I am not sure how to use that to write a Visual Basic statement that will execute the LINQ Query.  i.e.
    Dim vejpselection As VejpSelection = ???

    I would then take "vejpselection" to pass it to a "Details" View if "vejpselection" exists or the "Create" View if non-existant.
    • Edited by Fan Farron Tuesday, February 11, 2014 3:57 AM
    Tuesday, February 11, 2014 12:59 AM

Answers

  • Hello,

    They are actually similar except First() and FirstOrDefault().

    First(): When we know or expect the sequence to have at least one element. In other words, when it is an exceptional occurence when the sequence is empty.

    FirstOrDefault(): When we know that we will need to check whether there was an element or not. In other words, when it is legal for the sequence to be empty. We should not rely on exception handling for the check.

    So if we write:

    vejpselection = db.VejpSelections.Where(Function(s) s.Pid = id).FirstOrDefault()
    Then they are the same.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fan Farron Wednesday, February 12, 2014 1:52 AM
    Wednesday, February 12, 2014 1:47 AM
    Moderator

All replies

  • Hello,

    Dim vejpselectionQuery = From selection In db.VejpSelections
    
    Where selection.Pid = id
    
    Select selection
    

    The vejpselectionQuery above will be a collection type which can store multiple records.

    If we define vejpselectionQuery as VejpSelection type, then it should be an entity type, in other word, it can store one record. Then the query should be:

    Dim vejpselectionQuery As VejpSelection = (From selection In db.VejpSelections
    
    Where selection.Pid = id
    
    Select selection). FirstOrDefault()
    

    Using the FirstOrDefault() to return the first record if it has records.

    If you want to know more about LINQ with VB, please refer to link below:

    http://msdn.microsoft.com/en-us/library/bb763068.aspx

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 12, 2014 1:33 AM
    Moderator
  • Thanks! Using "FirstOrDefault()" worked.

    Question : what is the difference between your suggestion and this:

    vejpselection = db.VejpSelections.Where(Function(s) s.Pid = id).First()
    Wednesday, February 12, 2014 1:41 AM
  • Hello,

    They are actually similar except First() and FirstOrDefault().

    First(): When we know or expect the sequence to have at least one element. In other words, when it is an exceptional occurence when the sequence is empty.

    FirstOrDefault(): When we know that we will need to check whether there was an element or not. In other words, when it is legal for the sequence to be empty. We should not rely on exception handling for the check.

    So if we write:

    vejpselection = db.VejpSelections.Where(Function(s) s.Pid = id).FirstOrDefault()
    Then they are the same.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fan Farron Wednesday, February 12, 2014 1:52 AM
    Wednesday, February 12, 2014 1:47 AM
    Moderator
  • Excellent.

    What I did to avoid hitting the exception was add this :

    Dim vejpselectioncount As Integer = db.VejpSelections.Where(Function(s) s.Pid = id).Count()

    so that if the count is zero, then I will create a new record for the db.  If nonzero, then I can safely display it.

    Thanks for your prompt response and attention to the matter.

    Wednesday, February 12, 2014 1:51 AM