Devart Forums

Code: Select all

 var query = from c in this.ObjectContext.Reservation1.Top("100").Include("Customer_Reservation.Customer")
                        select c;

            return query;

Code: Select all

Table Definitions:


CREATE TABLE  `Test`.`Reservation` (
  `ReservationID` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT 'EMPTY',
  `OwnedBy` varchar(128) DEFAULT 'Current_User()',
  `DateCreated` datetime DEFAULT NULL,
  `CreatedBy` varchar(128) DEFAULT NULL,
  `GroupLeader` varchar(64) DEFAULT NULL,
  `GroupLeader_ReservationID` varchar(64) DEFAULT NULL,
  `Cancelled` tinyint(1) DEFAULT NULL,
  `Type` varchar(50) DEFAULT '',
  `CheckInDate` datetime DEFAULT NULL,
  `CheckOutDate` datetime DEFAULT NULL,
  `Confirmation_Num` varchar(50) DEFAULT '',
  `Arrival Time` varchar(50) DEFAULT NULL,
  `Notes` mediumtext,
  `RoomPrice` decimal(19,2) NOT NULL DEFAULT '0.00',
  `Extras` decimal(19,2) NOT NULL DEFAULT '0.00',
  `Deposit` decimal(19,2) DEFAULT '0.00',
  `IsLocked` tinyint(4) NOT NULL,
  `LockedBy` varchar(64) DEFAULT NULL,
  `RoomTax` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Rate` decimal(10,2) NOT NULL DEFAULT '0.00',
  `MiscItems` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TotalRentalCar` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TotalPackagePrice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `BalanceDue` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Source` varchar(64) DEFAULT NULL,
  `InitialNotes` mediumtext,
  `GolfandRoomNotes` mediumtext,
  `ClosingNotes` mediumtext,
  `AdvertiseRooms` tinyint(4) DEFAULT NULL,
  `AdvertiseGolf` tinyint(4) DEFAULT NULL,
  `AdvertiseRentalCar` tinyint(4) DEFAULT NULL,
  `SpecialInfo` tinyint(4) DEFAULT NULL,
  `SpecialInfoYear` varchar(48) DEFAULT NULL,
  `CompleteListCourses` tinyint(4) DEFAULT NULL,
  `CompleteListCoursesList` varchar(48) DEFAULT NULL,
  `ParentReservation` varchar(64) DEFAULT NULL,
  `RedDate` datetime DEFAULT NULL,
  `FollowUp` int(11) DEFAULT NULL,
  `CreatedByQuoteID` varchar(64) DEFAULT NULL,
  `DateTimeCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `SalesTax_RoomPrice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_RoomTax` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_Rate` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_Extras` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_TotalRentalCar` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_TotalPackagePrice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_Deposit` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_Balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `IsCharted` tinyint(4) NOT NULL DEFAULT '0',
  `DateCharted` datetime DEFAULT NULL,
  `ChartedBy` varchar(64) NOT NULL,
  `CancelledDate` datetime DEFAULT NULL,
  PRIMARY KEY (`ReservationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE  `Test`.`Customer_Reservation` (
  `ReservationID` varchar(64) CHARACTER SET utf8 NOT NULL,
  `CustomerID` varchar(64) CHARACTER SET utf8 NOT NULL,
  `Amount` decimal(19,4) NOT NULL,
  PRIMARY KEY (`ReservationID`,`CustomerID`),
  KEY `FK_Customer_Reservation_2` (`CustomerID`),
  KEY `FK_Customer_Reservation_1` (`ReservationID`),
  CONSTRAINT `FK_Customer_Reservation_1` FOREIGN KEY (`ReservationID`) REFERENCES `Reservation` (`ReservationID`),
  CONSTRAINT `FK_Customer_Reservation_2` FOREIGN KEY (`CustomerID`) REFERENCES `Customer` (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 


CREATE TABLE  `Test`.`Customer`(
  `CustomerID` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT 'new',
  `Sal` varchar(64) DEFAULT NULL,
  `First Name` varchar(50) DEFAULT NULL,
  `Last Name` varchar(50) DEFAULT NULL,
  `Country` varchar(50) DEFAULT NULL,
  `City` varchar(500) DEFAULT NULL,
  `State` varchar(50) DEFAULT NULL,
  `Street` varchar(50) DEFAULT NULL,
  `Zip` varchar(50) DEFAULT NULL,
  `House Number` varchar(50) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Email2` varchar(50) DEFAULT NULL,
  `Phone` varchar(50) DEFAULT NULL,
  `Cell Phone` varchar(50) DEFAULT NULL,
  `Work Phone` varchar(50) DEFAULT NULL,
  `Fax` varchar(50) DEFAULT NULL,
  `Source` varchar(64) DEFAULT NULL,
  `Type` varchar(45) NOT NULL DEFAULT 'Consumer',
  `Active` tinyint(3) unsigned NOT NULL,
  `Company Name` varchar(200) DEFAULT '',
  PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code: Select all

Query Explain extended SELECT Project1.C1 AS C1, Project1.ReservationID AS ReservationID, Project1.OwnedBy AS OwnedBy, Project1.DateCreated AS DateCreated, Project1.CreatedBy AS CreatedBy, Project1.GroupLeader AS GroupLeader, Project1.GroupLeader_ReservationID AS GroupLeader_ReservationID, Project1.Cancelled AS Cancelled, Project1.Type AS Type, Project1.CheckInDate AS CheckInDate, Project1.CheckOutDate AS CheckOutDate, Project1.Confirmation_Num AS Confirmation_Num, Project1.`Arrival Time` AS `Arrival Time`, Project1.Notes AS Notes, Project1.RoomPrice AS RoomPrice, Project1.Extras AS Extras, Project1.Deposit AS Deposit, Project1.IsLocked AS IsLocked, Project1.LockedBy AS LockedBy, Project1.RoomTax AS RoomTax, Project1.Rate AS Rate, Project1.MiscItems AS MiscItems, Project1.TotalRentalCar AS TotalRentalCar, Project1.TotalPackagePrice AS TotalPackagePrice, Project1.BalanceDue AS BalanceDue, Project1.Source AS Source, Project1.InitialNotes AS InitialNotes, Project1.GolfandRoomNotes AS GolfandRoomNotes, Project1.ClosingNotes AS ClosingNotes, Project1.AdvertiseRooms AS AdvertiseRooms, Project1.AdvertiseGolf AS AdvertiseGolf, Project1.AdvertiseRentalCar AS AdvertiseRentalCar, Project1.SpecialInfo AS SpecialInfo, Project1.SpecialInfoYear AS SpecialInfoYear, Project1.CompleteListCourses AS CompleteListCourses, Project1.CompleteListCoursesList AS CompleteListCoursesList, Project1.ParentReservation AS ParentReservation, Project1.RedDate AS RedDate, Project1.FollowUp AS FollowUp, Project1.CreatedByQuoteID AS CreatedByQuoteID, Project1.DateTimeCreated AS DateTimeCreated, Project1.SalesTax_RoomPrice AS SalesTax_RoomPrice, Project1.SalesTax_RoomTax AS SalesTax_RoomTax, Project1.SalesTax_Rate AS SalesTax_Rate, Project1.SalesTax_Extras AS SalesTax_Extras, Project1.SalesTax_TotalRentalCar AS SalesTax_TotalRentalCar, Project1.SalesTax_TotalPackagePrice AS SalesTax_TotalPackagePrice, Project1.SalesTax_Deposit AS SalesTax_Deposit, Project1.SalesTax_Balance AS SalesTax_Balance, Project1.IsCharted AS IsCharted, Project1.DateCharted AS DateCharted, Project1.ChartedBy AS ChartedBy, Project1.CancelledDate AS CancelledDate, Project1.C2 AS C2, Project1.ReservationID1 AS ReservationID1, Project1.CustomerID AS CustomerID, Project1.Amount AS Amount, Project1.CustomerID1 AS CustomerID1, Project1.Sal AS Sal, Project1.`First Name` AS `First Name`, Project1.`Last Name` AS `Last Name`, Project1.Country AS Country, Project1.City AS City, Project1.State AS State, Project1.Street AS Street, Project1.Zip AS Zip, Project1.`House Number` AS `House Number`, Project1.Email AS Email, Project1.Email2 AS Email2, Project1.Phone AS Phone, Project1.`Cell Phone` AS `Cell Phone`, Project1.`Work Phone` AS `Work Phone`, Project1.Fax AS Fax, Project1.Source1 AS Source1, Project1.Type1 AS Type1, Project1.Active AS Active, Project1.`Company Name` AS `Company Name` FROM ( SELECT Limit1.ReservationID AS ReservationID, Limit1.OwnedBy AS OwnedBy, Limit1.DateCreated AS DateCreated, Limit1.CreatedBy AS CreatedBy, Limit1.GroupLeader AS GroupLeader, Limit1.GroupLeader_ReservationID AS GroupLeader_ReservationID, Limit1.Cancelled AS Cancelled, Limit1.Type AS Type, Limit1.CheckInDate AS CheckInDate, Limit1.CheckOutDate AS CheckOutDate, Limit1.Confirmation_Num AS Confirmation_Num, Limit1.`Arrival Time` AS `Arrival Time`, Limit1.Notes AS Notes, Limit1.RoomPrice AS RoomPrice, Limit1.Extras AS Extras, Limit1.Deposit AS Deposit, Limit1.IsLocked AS IsLocked, Limit1.LockedBy AS LockedBy, Limit1.RoomTax AS RoomTax, Limit1.Rate AS Rate, Limit1.MiscItems AS MiscItems, Limit1.TotalRentalCar AS TotalRentalCar, Limit1.TotalPackagePrice AS TotalPackagePrice, Limit1.BalanceDue AS BalanceDue, Limit1.Source AS Source, Limit1.InitialNotes AS InitialNotes, Limit1.GolfandRoomNotes AS GolfandRoomNotes, Limit1.ClosingNotes AS ClosingNotes, Limit1.AdvertiseRooms AS AdvertiseRooms, Limit1.AdvertiseGolf AS AdvertiseGolf, Limit1.AdvertiseRentalCar AS AdvertiseRentalCar, Limit1.SpecialInfo AS SpecialInfo, Limit1.SpecialInfoYear AS SpecialInfoYear, Limit1.CompleteListCourses AS CompleteListCourses, Limit1.CompleteListCoursesList AS CompleteListCoursesList, Limit1.ParentReservation AS ParentReservation, Limit1.RedDate AS RedDate, Limit1.FollowUp AS FollowUp, Limit1.CreatedByQuoteID AS CreatedByQuoteID, Limit1.DateTimeCreated AS DateTimeCreated, Limit1.SalesTax_RoomPrice AS SalesTax_RoomPrice, Limit1.SalesTax_RoomTax AS SalesTax_RoomTax, Limit1.SalesTax_Rate AS SalesTax_Rate, Limit1.SalesTax_Extras AS SalesTax_Extras, Limit1.SalesTax_TotalRentalCar AS SalesTax_TotalRentalCar, Limit1.SalesTax_TotalPackagePrice AS SalesTax_TotalPackagePrice, Limit1.SalesTax_Deposit AS SalesTax_Deposit, Limit1.SalesTax_Balance AS SalesTax_Balance, Limit1.IsCharted AS IsCharted, Limit1.DateCharted AS DateCharted, Limit1.ChartedBy AS ChartedBy, Limit1.CancelledDate AS CancelledDate, 1 AS C1, Join1.ReservationID AS ReservationID1, Join1.CustomerID1 AS CustomerID, Join1.Amount AS Amount, Join1.CustomerID2 AS CustomerID1, Join1.Sal AS Sal, Join1.`First Name` AS `First Name`, Join1.`Last Name` AS `Last Name`, Join1.Country AS Country, Join1.City AS City, Join1.State AS State, Join1.Street AS Street, Join1.Zip AS Zip, Join1.`House Number` AS `House Number`, Join1.Email AS Email, Join1.Email2 AS Email2, Join1.Phone AS Phone, Join1.`Cell Phone` AS `Cell Phone`, Join1.`Work Phone` AS `Work Phone`, Join1.Fax AS Fax, Join1.Source AS Source1, Join1.Type AS Type1, Join1.Active AS Active, Join1.`Company Name` AS `Company Name`, CASE WHEN (Join1.ReservationID IS NULL) THEN cast(NULL as signed) ELSE 1 END AS C2 FROM (SELECT c.ReservationID AS ReservationID, c.OwnedBy AS OwnedBy, c.DateCreated AS DateCreated, c.CreatedBy AS CreatedBy, c.GroupLeader AS GroupLeader, c.GroupLeader_ReservationID AS GroupLeader_ReservationID, c.Cancelled AS Cancelled, c.Type AS Type, c.CheckInDate AS CheckInDate, c.CheckOutDate AS CheckOutDate, c.Confirmation_Num AS Confirmation_Num, c.`Arrival Time` AS `Arrival Time`, c.Notes AS Notes, c.RoomPrice AS RoomPrice, c.Extras AS Extras, c.Deposit AS Deposit, c.IsLocked AS IsLocked, c.LockedBy AS LockedBy, c.RoomTax AS RoomTax, c.Rate AS Rate, c.MiscItems AS MiscItems, c.TotalRentalCar AS TotalRentalCar, c.TotalPackagePrice AS TotalPackagePrice, c.BalanceDue AS BalanceDue, c.Source AS Source, c.InitialNotes AS InitialNotes, c.GolfandRoomNotes AS GolfandRoomNotes, c.ClosingNotes AS ClosingNotes, c.AdvertiseRooms AS AdvertiseRooms, c.AdvertiseGolf AS AdvertiseGolf, c.AdvertiseRentalCar AS AdvertiseRentalCar, c.SpecialInfo AS SpecialInfo, c.SpecialInfoYear AS SpecialInfoYear, c.CompleteListCourses AS CompleteListCourses, c.CompleteListCoursesList AS CompleteListCoursesList, c.ParentReservation AS ParentReservation, c.RedDate AS RedDate, c.FollowUp AS FollowUp, c.CreatedByQuoteID AS CreatedByQuoteID, c.DateTimeCreated AS DateTimeCreated, c.SalesTax_RoomPrice AS SalesTax_RoomPrice, c.SalesTax_RoomTax AS SalesTax_RoomTax, c.SalesTax_Rate AS SalesTax_Rate, c.SalesTax_Extras AS SalesTax_Extras, c.SalesTax_TotalRentalCar AS SalesTax_TotalRentalCar, c.SalesTax_TotalPackagePrice AS SalesTax_TotalPackagePrice, c.SalesTax_Deposit AS SalesTax_Deposit, c.SalesTax_Balance AS SalesTax_Balance, c.IsCharted AS IsCharted, c.DateCharted AS DateCharted, c.ChartedBy AS ChartedBy, c.CancelledDate AS CancelledDate FROM Test.Reservation AS c limit 100 ) AS Limit1 LEFT OUTER JOIN (SELECT Extent2.ReservationID AS ReservationID, Extent2.CustomerID AS CustomerID1, Extent2.Amount AS Amount, Extent3.CustomerID AS CustomerID2, Extent3.Sal AS Sal, Extent3.`First Name` AS `First Name`, Extent3.`Last Name` AS `Last Name`, Extent3.Country AS Country, Extent3.City AS City, Extent3.State AS State, Extent3.Street AS Street, Extent3.Zip AS Zip, Extent3.`House Number` AS `House Number`, Extent3.Email AS Email, Extent3.Email2 AS Email2, Extent3.Phone AS Phone, Extent3.`Cell Phone` AS `Cell Phone`, Extent3.`Work Phone` AS `Work Phone`, Extent3.Fax AS Fax, Extent3.Source AS Source, Extent3.Type AS Type, Extent3.Active AS Active, Extent3.`Company Name` AS `Company Name` FROM Test.Customer_Reservation AS Extent2 INNER JOIN Test.Customer AS Extent3 ON Extent2.CustomerID = Extent3.CustomerID ) AS Join1 ON Limit1.ReservationID = Join1.ReservationID ) AS Project1 ORDER BY Project1.ReservationID ASC, Project1.C2 ASC -- 0 row(s) affected., Sun Apr 25 12:04:40 2010 


id select_type table type possible_keys key key_len ref rows filtered Extra 
1 PRIMARY  ALL     121 100.00 Using filesort 
2 DERIVED  ALL     100 100.00 (null) 
2 DERIVED  ALL     22111 100.00 (null) 
4 DERIVED Extent2 ALL FK_Customer_Reservation_2    22202 100.00 (null) 
4 DERIVED Extent3 eq_ref PRIMARY PRIMARY 194 Test.Extent2.CustomerID 1 100.00 (null) 
3 DERIVED c ALL     15880 100.00 (null) 

Code: Select all

  System.Data.Objects.ObjectSet Reservations = this.ObjectContext.Reservation1;

  System.Data.Objects.ObjectSet Customer_Reservations = this.ObjectContext.Customer_Reservation1;

  System.Data.Objects.ObjectSet Customers = this.ObjectContext.Customer1;

            var query = from Resevation in Reservations.Top("20")
                        join dete in Customer_Reservations
                        on Resevation.ReservationID
                        equals dete.ReservationID
                        join cust in Customers
                        on dete.CustomerID
                        equals cust.CustomerID into CustGroup
                        select new { Resevation, CustGroup };