我是ASP.NET MVC5的新手。
我有一个模型Shipping
,我的dbo.Shippings.sql
在此处显示:
CREATE TABLE [dbo].[Shippings]
(
[Id] INT IDENTITY (1, 1) NOT NULL,
[TrackingNumber] NVARCHAR (MAX) NOT NULL,
[FromCompany] NVARCHAR (MAX) NULL,
[FromContactName] NVARCHAR (MAX) NULL,
[From] NVARCHAR (MAX) NOT NULL,
[FromCity] NVARCHAR (MAX) NOT NULL,
[FromState] NVARCHAR (MAX) NOT NULL,
[FromZipCode] NVARCHAR (MAX) NOT NULL,
[FromCountry] NVARCHAR (MAX) NOT NULL,
[FromPhoneNumber] NVARCHAR (MAX) NULL,
[FromFaxNumber] NVARCHAR (MAX) NULL,
[Commodity] NVARCHAR (MAX) NOT NULL,
[OrderDateTime] DATETIME NOT NULL,
[OrderByName] NVARCHAR (MAX) NOT NULL,
[PickUpDateTime] DATETIME NOT NULL,
[PickUpRefNum] NVARCHAR (MAX) NOT NULL,
[DestCompany] NVARCHAR (MAX) NULL,
[DestContactName] NVARCHAR (MAX) NULL,
[Destination] NVARCHAR (MAX) NOT NULL,
[DestCity] NVARCHAR (MAX) NOT NULL,
[DestState] NVARCHAR (MAX) NOT NULL,
[DestZipCode] NVARCHAR (MAX) NOT NULL,
[DestCountry] NVARCHAR (MAX) NOT NULL,
[DestPhoneNumber] NVARCHAR (MAX) NULL,
[DestFaxNumber] NVARCHAR (MAX) NULL,
[DeliveryDateTime] DATETIME NOT NULL,
[Price] FLOAT (53) NOT NULL,
[Category] NVARCHAR (MAX) NOT NULL,
[FreightClass] INT NOT NULL,
[Pkgs] INT NOT NULL,
[Weight] REAL NOT NULL,
[Length] REAL NOT NULL,
[Width] REAL NOT NULL,
[Height] REAL NOT NULL,
[IsDelivered] BIT NOT NULL,
[ApplicationUserId] NVARCHAR (128) NOT NULL,
[AcceptOffer] BIT DEFAULT ((0)) NOT NULL,
[TruckerId] NVARCHAR (MAX) NULL,
[Description] NVARCHAR (MAX) NULL,
[FileName] NVARCHAR (255) NULL,
[ContentType] NVARCHAR (100) NULL,
[Content] VARBINARY (MAX) NULL,
CONSTRAINT [PK_dbo.Shippings]
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.Shippings_dbo.AspNetUsers_ApplicationUserId]
FOREIGN KEY ([ApplicationUserId])
REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_ApplicationUserId]
ON [dbo].[Shippings]([ApplicationUserId] ASC);
我的第一页必须显示所有尚未出售的托运人(if shipping.truckerId == null
表示尚未出售)
在我的ShippingController
中,我有以下内容:
// GET: Shipping
public ActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
//below is sorting
ViewBag.CurrentSort = sortOrder;
ViewBag.FromSortParm = String.IsNullOrEmpty(sortOrder) ? "from_desc" : "";
ViewBag.DestSortParm = sortOrder == "Destination" ? "dest_desc" : "Destination";
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewBag.CurrentFilter = searchString;
//below is searching state and zipcode
var shippings = from s in db.Shippings
where s.TruckerId == null
select s;
if (!String.IsNullOrEmpty(searchString))
{
shippings = shippings.Where(s => s.FromState.Contains(searchString)//from state
|| s.DestState.Contains(searchString)//dest state
|| s.FromZipCode.Contains(searchString)//from zipcode
|| s.DestZipCode.Contains(searchString));//dest zipcode
}
switch (sortOrder)
{
case "from_desc":
shippings = shippings.OrderByDescending(s => s.From);
break;
case "Destination":
shippings = shippings.OrderBy(s => s.Destination); //not working
break;
case "dest_desc":
shippings = shippings.OrderByDescending(s => s.Destination); //not working
break;
default:
shippings = shippings.OrderByDescending(s => s.OrderDateTime);
break;
}
int pageSize = 10;
int pageNumber = (page ?? 1);
return View(shippings.ToPagedList(pageNumber, pageSize));
//return View(shippings.ToList());
}
我认为:
var shippings = from s in db.Shippings
where s.TruckerId == null
select s;
不是聪明的托运方式,如果数据库变大,那就非常慢。
请帮助!
显然您需要查看数据类型。实际上,您实际上需要许多VARCHAR(MAX)
列。因此,第一件事是从这个意义上重塑数据库。我强烈建议您在SQL Server Central上查看Joe Celko的数据系列楼梯,它将为您提供清晰的数据类型及其正确用法的图片。
using (var ctx = new MyDataContext())
{
var theDataINeed = (from myData in ctx.Shipping
where myData.TruckerId == null
select new {
myData.FromCity,
myData.FromState,
myData.FromZipCode,
myData.PickUpDateTime
});
}
然后将其传递给List<>
左右,您只会得到所需的东西。